# Find The Sum Of All Digits Occuring In A String

April 05, 2021 - by Bill Jelen

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:

Background: You could do this manually. You know that the digits that are significant 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 modified 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}, reflecting one occurrence each of 2 and 3, two occurrences of 4, and no occurrences of the other digits. At this point, the formula:

translates to:

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)

Alternate Strategy: If you replace SUM in the original formula with SUMPRODUCT and replace the multiplication sign * with a comma, you could enter the formula as:

This form would probably look a bit more intuitive to some users.

Illustrative Examples:

76432 22
*****(8,121) 12
76*432 22
764 test 32 22
1 test 2 3
156.546 27
3127543.791 44
t=18317; p=239317 45
24 / 12 9
30°54’43” 19
SSN 421-89-7322 38
800/555-1212 29
3.142 PI() displayed to 14 decimal points 77
06:00 PM Underlying value = 0.75 12

Summary: You can use SUM (or SUMPRODUCT) to build a formula that returns the sum of all the digits in a string of text.

Source: Adding numbers in String on the MrExcel Message Board.

Title Photo: Antoine Dautry on Unsplash