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

signiﬁ 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 modiﬁ 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}, reﬂ 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