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:

Seemingly incomprehensible, eh? Read on…

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:

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

This article is an excerpt from Excel Gurus Gone Wild.