从含有数字的文本字符串中提取出数字
我的工作表中有许多含有数字的单元格，我想将数字单独提取出来。如下图1所示，将列A的单元格中的数字提取出来放置在列B中，应该如何编写公式呢？
图1
可以使用数组公式：
=1*MID(A1,MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$20),1)),0),COUNT(1*MID(A1,ROW($1:$20),1)))
如下图2所示，下拉至单元格B4。
图2
公式中，使用MID(A1,ROW($1:$20),1)分解单元格A1中的文本，使用MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$20),1)),0)找到文本中数字的起始位置，使用COUNT(1*MID(A1,ROW($1:$20),1))来计算数字的个数。
然后，将数字的起始位置及个数作为MID函数的参数，提取出数字文本。最后，乘以1将文本转换成数字值。
编写公式的过程请参考下面的视频：
此外，可以使用下面的公式：
=LOOKUP(9.99999999E+307,1*LEFT(MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),999),ROW($1:$999)))
实现相同的目的。
明解C语言 中级篇
作者：[日]柴田望洋
上面的示例及公式解决了文本中数字是连续的情形。如果文本中的数字不连续呢？也就是说，文本中有好几处出现了数字呢，如下图3所示，使用上面的公式得到#VALUE!错误，我们如何把这些数字提取出来？
图3
先给出公式：
=SUM(MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"& LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"& LEN(A1)))-1))
仍然是数组公式，结果如下图4所示。
图4
这个公式很复杂，有点不好理解，下面我们来详细解释。
公式中的：
ROW(INDIRECT("1:"&LEN(A1)))
等价于：
ROW(INDIRECT(“1:”&14))
进一步转换为：
ROW(INDIRECT(“1:14”))
使用在数组公式中时会转换为数组：
{1;2;3;4;5;6;7;8;9;10;11;12;13;14}
公式中的：
MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)
等价于：
MID(“完美2018Excel923”,{1;2;3;4;5;6;7;8;9;10;11;12;13;14},1)
得到数组：
{"完";"美";"2";"0";"1";"8";"E";"x";"c";"e";"l";"9";"2";"3"}
公式中的：
ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)
等价于：
ISNUMBER({"完";"美";"2";"0";"1";"8";"E";"x";"c";"e";"l";"9";"2";"3"}/1)
由于数字文本除以数字将转换为数字，其他文本将返回错误，因此上述公式转换为：
ISNUMBER({#VALUE!;#VALUE!;2;0;1;8;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;9;2;3})
得到数组：
{FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}
公式中的：
LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"& LEN(A1))))
等价于：
LARGE({FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE}*{1;2;3;4;5;6;7;8;9;10;11;12;13;14},{1;2;3;4;5;6;7;8;9;10;11;12;13;14})
转换为：
LARGE({0;0;3;4;5;6;0;0;0;0;0;12;13;14},{1;2;3;4;5;6;7;8;9;10;11;12;13;14})
得到数组：
{14;13;12;6;5;4;3;0;0;0;0;0;0;0}
下面，来看公式中的：
MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"& LEN(A1))))+1,1)
转换为：
MID(“0完美2018Excel923”,{15;14;13;7;6;5;4;1;1;1;1;1;1;1},1)
等价于：
{"3";"2";"9";"8";"1";"0";"2";"0";"0";"0";"0";"0";"0";"0"}
最后，到了公式的关键部分。
我们知道，x^y表示x的y次方，这意味着：
MID(0&A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)/1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"& LEN(A1))))+1,1)*10^(ROW(INDIRECT("1:"& LEN(A1)))-1)
等价于：
{"3";"2";"9";"8";"1";"0";"2";"0";"0";"0";"0";"0";"0";"0"}*10^{0;1;2;3;4;5;6;7;8;9;10;11;12;13}
进一步转换为：
{"3";"2";"9";"8";"1";"0";"2";"0";"0";"0";"0";"0";"0";"0"}*{1;10;100;1000;10000;100000;1000000;10000000;100000000;1000000000;10000000000;100000000000;1000000000000;10000000000000}
得到数组：
{3;20;900;8000;10000;0;2000000;0;0;0;0;0;0;0}
将上述数组传递给SUM函数求和，得到结果：
2018923
也就是文本中的数字连接后的数字。
为什么要使用0&A1呢？这是为了保证在MID函数提取值时，避免由于参数是0而导致产生错误值。
下面，再给出两个公式。
公式2：
=SUM(IFERROR(MID(A1,LARGE(ISNUMBER(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)^1)*ROW(INDIRECT("1:"&LEN(A1))),ROW(INDIRECT("1:"&LEN(A1)))),1),0)*(1&REPT("0",(ROW(INDIRECT("1:"&LEN(A1)))-1))))
公式3：
=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$50),1))*ROW($1:$50),0),ROW($1:$50))+1,1)*10^ROW($1:$50)/10)
都可以得到相同的结果。