Adding digits of a number

O

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

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
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)...
Click to expand...

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
Click to expand...

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
You must log in or register to reply here.

Similar threads

serge
  • Solved
Return number into separate cells
Replies
3
Views
257
Joe4
Joe4
K
  • Solved
count numbers between dashes, keeping the dash
Replies
2
Views
56
Kmitchell
K
D
  • Solved
Matching TODAY() using VLOOKUP
Replies
7
Views
260
Peter_SSs
Peter_SSs
F
  • Solved
Add trailing zeros in VBA
Replies
2
Views
116
Flavien
F
gtd526
  • Question Question
Match Values in a different worksheet (odd rows)
Replies
0
Views
66
gtd526
gtd526

Forum statistics

Threads
1,224,903
Messages
6,181,648
Members
453,059
Latest member
jkevin

Share this page

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