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

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

81;8.75@5279@4.=45A？a；

我们希望公式能够返回：

818755279445

解决办法

相对简洁的数组公式：

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

原理分析

到目前为止，我们应该已经熟悉了ROW/间接函数的组合：

行(间接(“1:”透镜(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(间接(“1:”LEN(A1))，1)

转换为：

MID(“81；8.75@5279@4.=45A？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)

获取：

{";"；“一”；"? "；“一”；"";"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函数的语法是：

净现值(比率、值1、值2、值3、)

相当于计算以下数字的总和：

=值1/(1 rate)^1值2/(1 rate)^2值3/(1 rate)^3 …

为了生成所需的结果，需要将数组中的元素乘以10的连续幂，然后将结果相加。可以看出，如果为参数速率选择合适的值，该公式将提供准确的结果。因此，选择-0.9，不仅因为1-0.9显然是0.1，而且当使用来自指数1的0.1的连续幂时，我们得到：

0.1

0.01

0.001

0.0001

…

获得相应的：

10

100

1000

一万

…

因此，在示例中，生成的数组的第一个非空元素是0.5，将乘以10；第二个元素是0.4乘以100，第三个元素是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，大)(IF(ISNUMBER(0 MID(A1，Arry1，1))，Arry1)，ROW(间接(“1:”计数(0 mid(a1,arry1,1))))),1)*10^(row(indirect("1:"count(0 mid(a1，Arry1，1))))-1))

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

=ROW(间接(" 1:"LEN($A1)))

一对比，就会感叹这样巧妙的公式应用了，只能说佩服！