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

Your formula returns result as text. If you want to do calculation with returned numbers, wrap your formula with VALUE() or add +0 at the end or multiply everything by 1 to convert it to number — user11222393
– user11222393, Commented Dec 11, 2023 at 21:32
Quick Add on if some of the cells were blank as there are no number at the end of the string for the Right function to find. How would I turn those blanks into 0 — Cameron Mah
– Cameron Mah, Commented Dec 11, 2023 at 22:16
there is no sum function in your formula. Please post the formula you actually use. — teylyn
– teylyn, Commented Dec 11, 2023 at 22:36
Can you provide an example of what the image or string text looks like? — Sunny
– Sunny, Commented Dec 12, 2023 at 4:23

il_raffa · Accepted Answer · 2023-12-14 07:33:55Z

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
        )
     )
 ))

edited Dec 14, 2023 at 7:33

il_raffa

5,175145 gold badges35 silver badges41 bronze badges

answered Dec 12, 2023 at 19:28

Ashgabat

811 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel SUM Function = 0 after RIGHT function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related