MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Find The Sum Of All Digits Occuring In A String


April 05, 2021 - by Bill Jelen

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:

e9781615474011_i0132.jpg

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:

e9781615474011_i0133.jpg

translates to:

e9781615474011_i0134.jpg

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:

e9781615474011_i0135.jpg

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

Illustrative Examples:

Text Comments Result
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


This article is an excerpt from Excel Gurus Gone Wild.



Bill Jelen is the author / co-author of:

Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.