Add From String

[mgirvin]

Dear Smartest Excelers In The World,

I have 111.12 in cell A1, and there could be ending or leading spaces.

I want to add the digits like this1+1+1+1+2 to get 6

I have used this formula (entering it with the key strokes Ctrl + Shift + Enter):

=SUM(MID(TRIM(SUBSTITUTE(A1,".","")),ROW(INDIRECT("1:"&LEN(TRIM(SUBSTITUTE(A1,".",""))))),1)+0)

Is there a shorter formula or am I on the right rack?

 

[jbeaucaire]

There are array and volatile formulas that do this, but here is a UserDefinedFunction that seems to make simple work in realtime, if you already have macros enabled, this may work for you.

First add this function:

Function AddDigits(NumberString As String) As Integer
  
  AddDigits = 0
  For i = 1 To Len(NumberString)
    CurDigit = Mid(NumberString, i, 1)
    If IsNumeric(CurDigit) Then AddDigits = AddDigits + CurDigit
  Next i

End Function

(source)

Press Alt-F11 to open editor
Insert > Module
Paste in code above
Alt-F11 to close editor
Save your sheet.

Now use that function as =AddDigits(A1)

 

[Ron Coderre]

With
A1: (a number with leading and/or trailing spaces)

If the embedded numbers may contain a decimal point
AND
- will always be postive
- will always have 15 significant digits or less

Try this regular formula sums the digits:

B1: =SUMPRODUCT(--MID(TEXT(SUBSTITUTE(A1,".",""),REPT("0",15)),
ROW(INDIRECT("1:15")),1))

If A1: ' 123456.789
the formula returns: 45
...the sum of {1,2,3,4,5,6,7,8,9}

Is that something you can work with?

 

[mgirvin]

Dear jbeaucaire,

Totally Cool. That works like a charm! And logically so simple.



Dear Ron Coderre,

Yes, I can work with that! I have not seen your TEXT solution to get rid of spaces before. I love learning new tricks! Also, your formula reminds me to use SUMPRODUCT. I still have natural reflexes to use SUM and CTRL + Shift + Enter.

Totally EXCELlent!!!!