Adding digits of a number

orivera

New Member
Joined
Apr 21, 2002
Messages
16
Hello:

I am looking for a formula or function that will add together the separate digits from a number. Example $156,546.00 would add in another cell as 1+5+6+5+4+6+0+0 the result being 27. Does such a function or formula exist.

Thank you,

Orlando o_O
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Book1
ABCDEF
1$156,546.0027
2
3
4
5
Sheet3


Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
 
Upvote 0
You could also create a User Defined Function in VBA:

Code:
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

Paste this code in the VBE and then use the function =AddDigits(A1).

Hope this helps!
 
Upvote 0
Aladin and Mark have already suggested solutions that work with numbers. However, if the $ is part of the cell content (i.e., not part of the formatting), consider using:

=SUM(IF(ISNUMBER(0+MID(H11,ROW(INDIRECT("a1:a"&LEN(H11))),1)),0+MID(H11,ROW(INDIRECT("a1:a"&LEN(H11))),1),0))

where H11 contains the text of interest.
 
Upvote 0
tusharm said:
...However, if the $ is part of the cell content (i.e., not part of the formatting)...

Due to coercion...

{=SUM(MID(A1*1000,ROW(INDIRECT("1:"&LEN(A1*1000))),1)+0)}

...works for...

{=SUM(MID("$15,6546.00"*1000,ROW(INDIRECT("1:"&LEN("$15,6546.00"*1000))),1)+0)}

...too.
 
Upvote 0
I think Marks would be okay due to the multiplilication (albeit for a different reason - I'm assuming to capture digits after the decimal??). And Aladin's would work as well by modifying H11 to H11+0. Your's would work in a case the case of say 123,456USD where Mark's and Aladin's would fail, though.
 
Upvote 0
Better late than never, so here's an improvement, if you will, on Mark W.'s array formula in that it avoids the use of the INDIRECT function:

=SUM(MID(A1*1000,ROW(A1:INDEX(A:A,LEN(A1*1000))),1)+0)

It's also similar to one of J. Walkenbach's formulas ("Microsoft Excel 2000 Bible", pg. 470), although he uses the OFFSET rather than the INDEX function, and the VALUE function rather than numeric coercion. Because of this, his is a longer construction.

Finally, this formula will work if "$" is part of the cell content, and not just the result of formatting.

--Tom
 
Upvote 0
rrdonutz said:
Better late than never, so here's an improvement, if you will, on Mark W.'s array formula in that it avoids the use of the INDIRECT function:

=SUM(MID(A1*1000,ROW(A1:INDEX(A:A,LEN(A1*1000))),1)+0)

It's also similar to one of J. Walkenbach's formulas ("Microsoft Excel 2000 Bible", pg. 470), although he uses the OFFSET rather than the INDEX function, and the VALUE function rather than numeric coercion. Because of this, his is a longer construction.

Finally, this formula will work if "$" is part of the cell content, and not just the result of formatting.

--Tom

Tom,

That construct for replacing INDIRECT falls apart when a row is inserted before the formula cell. You need...

{=SUM(MID(A2*1000,ROW(INDEX(A:A,1):INDEX(A:A,LEN(A2*1000))),1)+0)}

This is similar to the throuble one runs into when using ROW(1:1) as a counter/indexer.

By the way, SUBSTITUTE(A1,".","") is better than using a fixed multiplier like 1000.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top