# 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:**

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.*