

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  
 
 A  B  C  D  E  F  1  $156,546.00   27     2        3        4        5        
[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 



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
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!

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 addins; 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 




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 


Aladin Akyurek
.
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: Originally posted by Aladin Akyurek:
By the way, SUBSTITUTE(A1,".","") is better than using a fixed multiplier like 1000.
This statement seems to be counterintuitive. 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
Hi Aladin,
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,"[^09]","")
Anyone who wants to learn more about regular expressions on a Wintel machine should check http://msdn.microsoft.com/library/default.asp?url=/library/enus/script56/html/reconIntroductionToRegularExpressions.asp and http://msdn.microsoft.com/library/default.asp?url=/library/enus/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: Originally posted by Aladin Akyurek: By the way, SUBSTITUTE(A1,".","") is better than using a fixed multiplier like 1000.
_________________ Tushar Mehta
Excel & PowerPoint tutorials and addins; custom productivity solutions for MS Office

Wed May 28, 2003 8:57 pm 



rrdonutz
Board Master
Joined: 16 Jan 2003
Posts: 437
Location: Round Rock, Texas
Flag:
Status: Offline

Re: Adding digits of a number
[Belated followup follows]:
Aladin,
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 nondecimal digits. (Haven't refined this last formulajust 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 


Aladin Akyurek
.
Joined: 15 Feb 2002
Posts: 13981
Location: The Hague
Flag:
Status: Offline

Re: Adding digits of a number
quote: Originally posted by rrdonutz: [Belated followup follows]:
Aladin,
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 nondecimal digits. (Haven't refined this last formulajust 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 


Aladin Akyurek
.
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 



