问与答117：如何求出字符串中出现的所有数字之和？

excelperfect

Q：如何使用公式返回文本字符串中所有数字之和？例如，对于文本字符串“I am 24years old and my Dad is 43”，应用公式后，结果为13，即2+4+4+3。

A：假设文本位于单元格A1中，在单元格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})

解析：

加法运算的有效数字是1到9，因此求和的算法是：

1 ×字符串中1的个数 +

2 × 字符串中2的个数 +

…

…

9 ×字符串中9的个数

= 结果

考虑使用SUBSTITUTE函数用空字符串替换所有出现的数字，例如4，SUBSTITUTE(Txt, 4,“”)返回没有4的文本（对于示例，返回“I am 2years old, and my Dad is 3”）。

考虑公式中的

SUBSTITUTE(A1, {1,2,3,4,5,6,7,8,9}, “”)

使用空字符串替换数字1至9，生成9个修改的字符串值数组，剔除了所有出现的相应的数字。

由于在字符串中数字4出现了2次，结果字符串比原字符串的长度少2，因此LEN(A1)-LEN(SUBSTITUTE(A1,4, “”)得到2。更进一步，公式中的：

LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},''))

得到含有9个值的数组，代表每个数字在字符串中出现的次数。即{0,1,1,2,0,0,0,0,0}，表示2和3都只出现了1次，4出现了2次，而其它的数字没有出现。这样，公式转换为：

=SUM({0,1,1,2,0,0,0,0,0}*{1,2,3,4,5,6,7,8,9})

两个数组对应元素相乘后求后，即：

=0×1+1×2+1×3+2×4+0×5+0×6+0×7+0×8+0×9

=13

可以使用SUMPRODUCT函数代替原公式中的SUM函数，此时公式为：

=SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{1,2,3,4,5,6,7,8,9},''))),{1,2,3,4,5,6,7,8,9})

注：本文整理自mrexcel.com，供有兴趣的朋友学习参考。