#### mgirvin

##### Well-known Member
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?

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

#### jbeaucaire

##### Well-known Member
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.

Code:
``````Function AddDigits(NumberString As String) As Integer

For i = 1 To Len(NumberString)
CurDigit = Mid(NumberString, i, 1)
Next i

End Function``````
(source)

Press Alt-F11 to open editor
Insert > Module
Paste in code above
Alt-F11 to close editor

Now use that function as =AddDigits(A1)

#### Ron Coderre

##### MrExcel MVP
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:
Code:
``````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

##### Well-known Member
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!!!!

Replies
7
Views
363
Replies
5
Views
372
Replies
4
Views
335
Replies
6
Views
491
Replies
7
Views
576

1,195,582
Messages
6,010,581
Members
441,557
Latest member
Jbest23

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back