Microsoft Office Tutorials and References
In Depth Information
FIND THE SUM OF ALL DIGITS OCCURING IN A STRING
FIND THE SUM OF ALL DIGITS
OCCURING IN A STRING
Challenge: You want to build a formula to return the sum of all the digits in a
string of text. For example, applying the formula on the text string “ I am 24
years old and my Dad is 43 " should yield 13 (2+4+4+3).
Setup: Assume that the text is in cell A1. Enter/copy the following formula in
B1:
=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})
Seemingly incomprehensible, eh? Read on…
Background: You could do this manually. You know that the digits that are
signifi cant for an addition operation are the digits 1 through 9. So an algorithm
of the sum you are looking for would be:
1 × the number of 1s in the string +
2 × the number of 2s in the string +
9 × the number of 9s in the string = RESULT
You could consider substituting all occurrences of a digit (say, 4) with a null
string, using the SUBSTITUTE function. SUBSTITUTE(Txt,4,"") returns
the text without any 4s (i.e., ‘I am 2 years old, and my Dad is 3’).
Consider the formula fragment SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},
"") . This successively substitutes the digits 1 through 9 with a null string, to
yield an array of 9 modifi ed string values, stripped of all occurrences of the
corresponding digits.
Because the number of 4s in the string is 2, the length of the resultant
string is 2 less than that of the original: LEN(A1) . Thus LEN(A1)-
LEN(SUBSTITUTE(A1,4,"") gives you 2. Accordingly, one step further up
the structure of the formula, LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},""))
gives you an array of 9 values, indicating the number of occurrences of each
digit in the string. The array is {0,1,1,2,0,0,0,0,0}, refl ecting one occurrence
each of 2 and 3, two occurrences of 4, and no occurrences of the other digits.
At this point, the formula:
=SUM((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},"")))*{1,2,3,4,5,6,7,8,9})
translates to:
=SUM({0,1,1,2,0,0,0,0,0} *{1,2,3,4,5,6,7,8,9})
This is the summation of products of corresponding elements of two arrays:
0×1 + 1×2 + 1×3 + 2×4 + 0×5 + 0×6 + 0×7 + 0×8 + 0×9 = 13 (Required
result)
Part
I
 
 
Search JabSto ::




Custom Search