Excel SUM Function = 0 after RIGHT function

Question

I used this function

=RIGHT(D2,
   LEN(D2) - 
      MAX(
        IF(ISNUMBER(MID(D2, ROW(INDIRECT("1:"&LEN(D2))), 1) *1)=FALSE,
            ROW(INDIRECT("1:"&LEN(D2))), 
           0
        )
     )
 ) 

to remove only the right most numbers from a string mixed of letters, special characters, and numbers. I am trying to do some basic calculations with the values of the new column however the SUM function is only returning 0

I have tried with the data being General and Number, I have also tried using Text to column solution but they both don't seem to work

Answer 1

This modified formula will extract the rightmost numbers from the string and convert them to numeric values in a single step.

=VALUE(RIGHT(D2,
   LEN(D2) - 
      MAX(
        IF(ISNUMBER(MID(D2, ROW(INDIRECT("1:"&LEN(D2))), 1) *1)=FALSE,
            ROW(INDIRECT("1:"&LEN(D2))), 
           0
        )
     )
 ))