Adding digits of a number

[orivera]

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

 

[Mark W.]

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.

 

[tusharm]

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/d...tml/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.

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

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

 

[Mark W.]

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

 

[rrdonutz]

[Belated follow-up follows]:
Aladin,

Thanks for this tip:

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

 

[Aladin Akyurek]

[Belated follow-up follows]:
Aladin,

Thanks for this tip:

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.

 

[Aladin Akyurek]

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

 

[NateO]

Related thread:

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