Adding digits of a number :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

orivera
Board Regular

Joined: 22 Apr 2002
Posts: 15

Flag:

Status: Offline

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

Wed May 28, 2003 2:19 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Adding digits of a number

 Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 C1 =

A
B
C
D
E
F
1
\$156,546.00  27
2

3

4

5

 Sheet3

[HtmlMaker 2.32] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

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".

Wed May 28, 2003 2:26 pm

.

Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag:

Status: Offline

Re: Adding digits of a number

One way...

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

Wed May 28, 2003 2:27 pm

LTunnicliffe
Board Master

Joined: 08 May 2003
Posts: 725
Location: Dallas, TX
Flag:

Status: Offline

Re: Adding digits of a number

You could also create a User Defined Function in VBA:

code:

Function AddDigits(NumberString As String) As Integer

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

End Function

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

Hope this helps!

Wed May 28, 2003 2:29 pm

tusharm
MrExcel MVP

Joined: 29 May 2002
Posts: 2702

Flag:

Status: Offline

Re: Adding digits of a number

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.
_________________
Tushar Mehta
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office

Wed May 28, 2003 2:35 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Adding digits of a number

quote:
Originally posted by tusharm:
...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.

Wed May 28, 2003 2:40 pm

IML
MrExcel MVP

Joined: 16 Feb 2002
Posts: 1360
Location: Denver, CO
Flag:

Status: Offline

Re: Adding digits of a number

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.

Wed May 28, 2003 2:42 pm

orivera
Board Regular

Joined: 22 Apr 2002
Posts: 15

Flag:

Status: Offline

Re: Adding digits of a number

Thank you very much guys.

Orlando

Wed May 28, 2003 2:49 pm

rrdonutz
Board Master

Joined: 16 Jan 2003
Posts: 437
Location: Round Rock, Texas
Flag:

Status: Offline

Re: Adding digits of a number

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

Wed May 28, 2003 7:37 pm

.

Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag:

Status: Offline

Re: Adding digits of a number

quote:
Originally posted by rrdonutz:
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.

Wed May 28, 2003 7:49 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Adding digits of a number

quote:

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

This statement seems to be counter-intuitive. I don't know exactly how SUBSTITUTE is implemented, but I imagine that it examines each character of a string it turn, to see if it's a "." and then replace it with "" if its is. I would guess that this takes considerably more processor cycles that a simple multiplication -- especially as the value's length increases!

P.S., I got a bit carried away with my multiplier -- 100 would suffice.

Wed May 28, 2003 8:06 pm

tusharm
MrExcel MVP

Joined: 29 May 2002
Posts: 2702

Flag:

Status: Offline

Re: Adding digits of a number

Good point about the Substitute. It's unfortunate the function is restricted to single token.

On my list of background tasks has been a way to provide regular expression capability as an XL function. This discussion led me to take a few minutes and write skeletal functions. Maybe, someone else will build on the skeletons and share the results.

Why regexp capability in XL? It would allow for significantly enhanced Find and Substitute functions. Specific to this discussion, one could substitute multiple characters with the empty string in one step, as in =RegExpSubstitute(D17,"[\$|\.]",""). In fact, the way to eliminate all non digits would be =RegExpSubstitute(D17,"[^0-9]","")

Anyone who wants to learn more about regular expressions on a Wintel machine should check http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/reconIntroductionToRegularExpressions.asp and http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsobjRegExp.asp

As an aside, I wonder if the functions below would work with Laurent Longre's SetV and GetV functions.

code:
Function RegExpSubstitute(ReplaceIn, ReplaceWhat, ReplaceWith)
Dim x As RegExp
Set x = New RegExp
x.Pattern = ReplaceWhat
x.Global = True
RegExpSubstitute = x.Replace(ReplaceIn, ReplaceWith)
End Function
Function RegExpFind(FindIn, FindWhat)
Dim x As RegExp, i As Long, allMatches As MatchCollection, aMatch As Match
Set x = New RegExp
x.Pattern = FindWhat
'x.ignorecase=true
x.Global = True
Set allMatches = x.Execute(FindIn)
ReDim rslt(0 To allMatches.Count - 1)
For i = 0 To allMatches.Count - 1
rslt(i) = allMatches(i).Value
Next i
RegExpFind = rslt
End Function

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

_________________
Tushar Mehta
Excel & PowerPoint tutorials and add-ins; custom productivity solutions for MS Office

Wed May 28, 2003 8:57 pm

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

Re: Adding digits of a number

tusharm, why is SUBSTITUTE more efficient then a low level operation such as multiplication?

Wed May 28, 2003 9:05 pm

rrdonutz
Board Master

Joined: 16 Jan 2003
Posts: 437
Location: Round Rock, Texas
Flag:

Status: Offline

Re: Adding digits of a number

[Belated follow-up follows]:

Thanks for this tip:

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

However, I think you meant to enter:

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

as a fix, rather than referencing A2 (for parallelism in the example).

Also, concerning your comments on the preference of SUBSTITUTE to a fixed multiplier (such as 1000)--SUBSTITUTE fails the "\$ as part of the cell" test. One possible fix to using 1000: use a multiplier such as 10^B1, where B1 is a formula that equals 20 minus the number of non-decimal digits. (Haven't refined this last formula--just too esoteric to chase. In any case, that would handle just about every conceivable decimal construction that Excel allows.)

--Tom

Wed May 28, 2003 9:14 pm

.

Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag:

Status: Offline

Re: Adding digits of a number

quote:
Originally posted by rrdonutz:
[Belated follow-up follows]:

Thanks for this tip:

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

However, I think you meant to enter:

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

as a fix, rather than referencing A2 (for parallelism in the example).

Also, concerning your comments on the preference of SUBSTITUTE to a fixed multiplier (such as 1000)--SUBSTITUTE fails the "\$ as part of the cell" test. One possible fix to using 1000: use a multiplier such as 10^B1, where B1 is a formula that equals 20 minus the number of non-decimal digits. (Haven't refined this last formula--just too esoteric to chase. In any case, that would handle just about every conceivable decimal construction that Excel allows.)

--Tom

Yes, we can have 10^LEN(A1) in order to construct an appropriate multiplier. That means an operator plus a function call, one reason why I just dropped the idea.

Wed May 28, 2003 9:25 pm

.

Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag:

Status: Offline

Re: Adding digits of a number

quote:
Originally posted by tusharm:
...On my list of background tasks has been a way to provide regular expression capability as an XL function. This discussion led me to take a few minutes and write skeletal functions. Maybe, someone else will build on the skeletons and share the results...

I'll check that out. It reminds me of MSUBSTITUTE Juan coded...

http://www.mrexcel.com/board2/viewtopic.php?t=18317&highlight=msubst%2A

Wed May 28, 2003 9:41 pm

NateO
MrExcel MVP

Joined: 18 Feb 2002
Posts: 4309
Location: Minneapolis, Mn, USA
Flag:

Status: Offline

Re: Adding digits of a number

http://www.mrexcel.com/board2/viewtopic.php?t=53620

Fri Jun 20, 2003 8:21 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum