excel公式技能:将所有数字提取到单个单元格-英雄云拓展知识分享

本文研究从字符串中提取所有数字并将这些数字作为单个数字放置在单个单元格中的技术。

本文使用与上一篇文中相同的字符串：

81;8.75>@5279@4.=45>A?A;

我们希望公式能够返回：

818755279445

解决方案

相对简洁的数组公式：

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”& LEN(A1))),1)/10,””))

原理解析

现在，我们应当很熟习ROW/INDIRECT函数组合了：

ROW(INDIRECT(“1:” & LEN(A1)))

生成由1至单元格A1中的字符串长度数组成的数组，本例中A1里的字符串长度为24，因此得到：

{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}

由1+LEN(A1)=25减去该数组，即：

25-{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}

得到：

{24;23;22;21;20;19;18;17;16;15;14;13;12;11;10;9;8;7;6;5;4;3;2;1}

即公式中MID函数的参数start_num的值，这样：

MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1))),1)

转换为：

MID(“81;8.75>@5279@4.=45>A?A;”,{24;23;22;21;20;19;18;17;16;15;14;13;12;11;10;9;8;7;6;5;4;3;2;1},1)

得到：

{“;”;”A”;”?”;”A”;”>”;”5″;”4″;”=”;”.”;”4″;”@”;”9″;”7″;”2″;”5″;”@”;”>”;”5″;”7″;”.”;”8″;”;”;”1″;”8″}

再由10除这个数组，得到：

{#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;0.5;0.4;#VALUE!;#VALUE!;0.4;#VALUE!;0.9;0.7;0.2;0.5;#VALUE!;#VALUE!;0.5;0.7;#VALUE!;0.8;#VALUE!;0.1;0.8}

传递给IFERROR函数，得到：

{“”;””;””;””;””;0.5;0.4;””;””;0.4;””;0.9;0.7;0.2;0.5;””;””;0.5;0.7;””;0.8;””;0.1;0.8}

继续之前，我们先看看NPV函数。

NPV函数具有一个好特质，可以疏忽传递给它的数据区域中的空格，仅按从左至右的顺序操作数据区域内的数值。

NPV函数的语法为：

NPV(rate,value1,value2,value3,,,)

等价于计算以下数的和：

=value1/(1+rate)^1+value2/(1+rate)^2+value3/(1+rate)^3+…

为了生成想要的结果，需将数组中的元素乘以连续的10的幂，然后将结果相加，可以看到，如果为参数rate选择适合的值，此公式将为会提供精确的结果。因此，这种情况下，选择-0.9，不但由于1-0.9明显是0.1，而且从指数1开始采取0.1的连续幂时，得到：

0.1

0.01

0.001

0.0001

…

相应地得到：

10

100

1000

10000

…

因此，这种情况下，在示例中，生成的数组的第一个非空元素是0.5，将乘以10；第2个元素0.4乘以100，第3个元素0.4乘以1000，依此类推。

这样，公式：

=NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”& LEN(A1))),1)/10,””))

转换成：

=NPV(-0.9,{“”;””;””;””;””;0.5;0.4;””;””;0.4;””;0.9;0.7;0.2;0.5;””;””;0.5;0.7;””;0.8;””;0.1;0.8})

得到：

818755279445

注意，应对单元格进行格式设置，否则可能结果是货币情势或指数情势。也能够在公式中添加一个INT函数来确保输出的是整数：

=INT(NPV(-0.9,IFERROR(MID(A1,1+LEN(A1)-ROW(INDIRECT(“1:”&LEN(A1))),1)/10,””)))

其实，还有更复杂的公式可以实现，例如数组公式：

=SUM(MID(A1,LARGE(IF(ISNUMBER(0+MID(A1,Arry1,1)),Arry1),ROW(INDIRECT(“1:”&COUNT(0+MID(A1,Arry1,1))))),1)*10^(ROW(INDIRECT(“1:”&COUNT(0+MID(A1,Arry1,1))))⑴))

公式中的Arry1是定义内涵的名称：

=ROW(INDIRECT(“1:”&LEN($A1)))

一对照，就会感叹这样奇妙的公式利用了，只能说佩服！