本文研究从字符串中提取所有数字并将这些数字作为单个数字放置在单个单元格中的技术 。
本文使用与上一篇文中相同的字符串：
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
…
相应地得到：
【excel公式技巧:将所有数字提取到单个单元格】10
100
1000
10000
…
因此 ， 在示例中 ， 生成的数组的第一个非空元素是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函数来确保输出的是整数：
推荐阅读
- Excel公式：从多列中返回唯一且按字母顺序排列的列表
- Excel公式技巧：将所有数字分别提取到不同的单元格
- excel公式技巧：从字符串中提取数字——数字位于字符串末尾
- word文档和excel的区别
- 独特风格的欧美花园设计技巧
- Excel公式练习42： 统计句子中满足条件的单词个数
- Excel公式技巧：从字符串中提取数字——数字位于字符串开头
- Excel公式： 获取非连续单元格区域中只出现一次的数字
- Excel公式技巧：十进制数转换成指定进制的数
- excel公式技巧：从单元格区域的字符串中提取唯一值