Adding digits of a number :: MrExcel Message Board


 FAQFAQ
   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   FavoritesFavorites   StatisticsStatistics 
 RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
Online StoreOnline Store

MrExcel Message Board Forum Index -> Excel Questions

Adding digits of a number
Post new topic   Reply to topic
Last Thread | Next Thread  >   Printable version
  Author    Thread

orivera
Board Regular


Joined: 22 Apr 2002
Posts: 15

Flag: Puertorico

Status: Offline

 Reply with quote  

Adding digits of a number

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 icon_iwantatten.gif

Post Wed May 28, 2003 2:19 pm 
 View user's profile Send private message

Mark W.
MrExcel MVP


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

Status: Offline

 Reply with quote  

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

Post Wed May 28, 2003 2:26 pm 
 View user's profile Send private message

Aladin Akyurek
.


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

Status: Offline

 Reply with quote  

Re: Adding digits of a number

One way...

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

Post Wed May 28, 2003 2:27 pm 
 View user's profile Send private message

LTunnicliffe
Board Master


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

Status: Offline

 Reply with quote  

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!

Post Wed May 28, 2003 2:29 pm 
 View user's profile Send private message

tusharm
MrExcel MVP


Joined: 29 May 2002
Posts: 2702

Flag: Blank

Status: Offline

 Reply with quote  

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

Post Wed May 28, 2003 2:35 pm 
 View user's profile Send private message Send e-mail Visit poster's website

Mark W.
MrExcel MVP


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

Status: Offline

 Reply with quote  

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.

Post Wed May 28, 2003 2:40 pm 
 View user's profile Send private message

IML
MrExcel MVP


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

Status: Offline

 Reply with quote  

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.

Post Wed May 28, 2003 2:42 pm 
 View user's profile Send private message Send e-mail

orivera
Board Regular


Joined: 22 Apr 2002
Posts: 15

Flag: Puertorico

Status: Offline

 Reply with quote  

Re: Adding digits of a number

Thank you very much guys.

Orlando icon_biggrin.gif

Post Wed May 28, 2003 2:49 pm 
 View user's profile Send private message

rrdonutz
Board Master


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

Status: Offline

 Reply with quote  

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

Post Wed May 28, 2003 7:37 pm 
 View user's profile Send private message

Aladin Akyurek
.


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

Status: Offline

 Reply with quote  

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.

Post Wed May 28, 2003 7:49 pm 
 View user's profile Send private message

Mark W.
MrExcel MVP


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

Status: Offline

 Reply with quote  

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

Post Wed May 28, 2003 8:06 pm 
 View user's profile Send private message

tusharm
MrExcel MVP


Joined: 29 May 2002
Posts: 2702

Flag: Blank

Status: Offline

 Reply with quote  

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,"[^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:
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 add-ins; custom productivity solutions for MS Office

Post Wed May 28, 2003 8:57 pm 
 View user's profile Send private message Send e-mail Visit poster's website

Mark W.
MrExcel MVP


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

Status: Offline

 Reply with quote  

Re: Adding digits of a number

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

Post Wed May 28, 2003 9:05 pm 
 View user's profile Send private message

rrdonutz
Board Master


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

Status: Offline

 Reply with quote  

Re: Adding digits of a number

[Belated follow-up 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 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

Post Wed May 28, 2003 9:14 pm 
 View user's profile Send private message

Aladin Akyurek
.


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

Status: Offline

 Reply with quote  

Re: Adding digits of a number

quote:
Originally posted by rrdonutz:
[Belated follow-up 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 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.

Post Wed May 28, 2003 9:25 pm 
 View user's profile Send private message

Aladin Akyurek
.


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

Status: Offline

 Reply with quote  

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

Post Wed May 28, 2003 9:41 pm 
 View user's profile Send private message

NateO
MrExcel MVP


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

Status: Offline

 Reply with quote  

Re: Adding digits of a number

Related thread:

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

Post Fri Jun 20, 2003 8:21 pm 
 View user's profile Send private message
  Display posts from previous:      

MrExcel Message Board Forum Index -> Excel Questions


Forum Jump:
Jump to:  

Post new topic   Reply to topic
Page 1 of 1



Add To Favorites

 


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

Powered by phpBB: 2.0.4 © 2001 phpBB Group

Need help posting your first question? Read how to post

Need extra help ? Couldn't get the answer you needed ? Get a free quote from our Consulting Team

Download Colo's HTML Maker utility for displaying your Excel Worksheet on the board.

Download VB HTML Maker to post your code on the board


Check out our new index to 485 Excel Articles.


Return to MrExcel Consulting

All contents Copyright 1998-2004 by MrExcel.com
If you believe information posted here is from your copyrighted source, notify us per the Terms of Use
Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.