Excel公式技巧：将所有数字分别提取到不同的单元格

本文研究从字符串中提取所有数字的技术：

1. 字符串由数字、字母和特殊字符组成

2. 数字在字符串的任意地方

3. 字符串中的小数也一样提取

3. 想要的结果是将所有数字返回独立的单元格

例如，在单元格A1中的字符串：

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

返回：

单元格B1：81

单元格C1：8.75

单元格D1：5279

单元格E1：4

单元格F1：45

解决方案

首先，确保活动单元格处于工作表行1中，然后定义下面两个名称。

名称：Arry1

引用位置：=ROW(INDIRECT(“1:”&LEN(“α”&$A1&”α0”)-1))

名称：Arry2

引用位置：=MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α”&$A1&”α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})

在单元格B1中输入数组公式：

=IFERROR(0+MID(“α”& $A1 &”α0″,1+SMALL(IF(Arry2=2,Arry1),COLUMNS($A:A)),SUM(SMALL(IF(ISNUMBER(MATCH(Arry2,{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})),””)

向右拖放直至出现空单元格为止。

原理解析

1. 先看看这两个定义的名称。对于Arry1：

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

生成由整数构成的数组。注意，在单元格A1的字符串前面添加了一个非数字字符“α”，在末尾添加了一个非数字字符和一个数字“α0”。为什么这样处理？具体原因在后文详述。

上述公式转换为：

=ROW(INDIRECT(“1:”&27-1))

结果为：

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

2. 对于Arry2：

=MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α”&$A1&”α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})

（1）公式通过引用ASCⅡ字符代码来测试单元格A1里的数字。

（2）要识别数字子字符串，必须找到字符串里两个不同的位置：一个对应着数字的起始位置，另一个对应着数字的结束位置。

（3）公式中的第一部分将给MID函数提供参数start_num，在生成的结果之间的减法提供相应的参数num_chars。

（4）对于0-9范围的整数的ASCⅡ编码从48到57，小数点是46。因此，如果首先从字符串中排除与ASCⅡ编码47相对应的任何字符（“/”），那么可以确定字符串中ASCⅡ编码在46-57范围内的任何字符要么是数字要么是小数点。

（5）使用字符的ASCⅡ编码减51.5，判断其结果的绝对值，如果小于或等于6，则可以判断该字符是数字或小数点。（这里运用的技巧等价于通常要使用的两个单独的条件判断，即一个来比较ASCⅡ编码大于45，另一个来比较ASCⅡ编码小于58。）

（6）注意，为了发现数字的开始位置和结束位置，这里查找字符串里的两对字符：一对中的第一个字符是非数字字符而第二个是数字字符（提供数字字符串的开始），另一对中的第一个字符是数字字符而第二个是非数字字符（提供数字字符串的结尾）。

（7）当然，如果字符串中的第一个或最后一个字符与上述标准相符，那么需要确保有一些字符在它们的前面或后面，这就是我们在A1的开头和结尾连接合适的字符串的原因。于是，就有了你所看见的”α”&和&”α0″。

这样，Arry2公式转换为：

MMULT(0+(ABS(51.5-CODE(MID(SUBSTITUTE(“α81;8.75>@5279@4.=45>A?A;α0″,”/”,”α”),Arry1+{0,1},1)))>6)*{2,1},{1;1})

转换为：

MMULT(0+(ABS(51.5-CODE(MID(“α81;8.75>@5279@4.=45>A?A;α0”,Arry1+{0,1},1)))>6)*{2,1},{1;1})

将Arry1代入，得到：

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

接下来是值得关注的技术之一。因为希望从字符串里考虑成对的字符，所以需要将字符串里位置1中的字符和位置2中的字符比较、位置2中的字符和位置3中的字符比较，依此类推。为了实现这个目的，需要生成传递给MID函数作为参数start_num的数组：{1,2;2,3;3,4;4,5;5,6;…}。

由于Arry1为{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行1列的数组，将其与一个1行2列的数组{0,1}相加，结果是一个24行2列的数组：

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

转换为：

MMULT(0+(ABS(51.5-CODE({“α”,”8″;”8″,”1″;”1″,”;”;”;”,”8″;”8″,”.”;”.”,”7″;”7″,”5″;”5″,”>”;”>”,”@”;”@”,”5″;”5″,”2″;”2″,”7″;”7″,”9″;”9″,”@”;”@”,”4″;”4″,”.”;”.”,”=”;”=”,”4″;”4″,”5″;”5″,”>”;”>”,”A”;”A”,”?”;”?”,”A”;”A”,”;”}))>6)*{2,1},{1;1})

转换为：

MMULT(0+(ABS(51.5-{63,56;56,49;49,59;59,56;56,46;46,55;55,53;53,62;62,64;64,53;53,50;50,55;55,57;57,64;64,52;52,46;46,61;61,52;52,53;53,62;62,65;65,63;63,65;65,59})>6)*{2,1},{1;1})

转换为：

MMULT(0+({11.5,4.5;4.5,2.5;2.5,7.5;7.5,4.5;4.5,5.5;5.5,3.5;3.5,1.5;1.5,10.5;10.5,12.5;12.5,1.5;1.5,1.5;1.5,3.5;3.5,5.5;5.5,12.5;12.5,0.5;0.5,5.5;5.5,9.5;9.5,0.5;0.5,1.5;1.5,10.5;10.5,13.5;13.5,11.5;11.5,13.5;13.5,7.5}>6)*{2,1},{1;1})

转换为：

MMULT({1,0;0,0;0,1;1,0;0,0;0,0;0,0;0,1;1,1;1,0;0,0;0,0;0,0;0,1;1,0;0,0;0,1;1,0;0,0;0,1;1,1;1,1;1,1;1,1}*{2,1},{1;1})

下面来看看现在得到的这个数组中的值代表的意思，我们高亮显示4组数字为例：

{1,0;0,0;0,1;1,0;0,0;0,0;0,0;0,1;1,1;1,0;0,0;0,0;0,0;0,1;1,0;0,0;0,1;1,0;0,0;0,1;1,1;1,1;1,1;1,1}

从MID函数中得到的字符数组：

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

（1）第1个高亮显示的对是{0,1}，相应的字符是{“1”,”;”}，因为“1”是数字而“;”不是。

（2）第2个高亮显示的对是{1,1}，相应的字符是{“>”,”@”}，因为”>”和”@”都不是数字。

（3）第3个高亮显示的对是{1,0}，相应的字符是{“@”,”4″}，由非数字和数字组成。

（4）第4个高亮显示的对是{0,0}，相应的字符是{“4″,”5”}，都是数字。

现在需要一种方法来区分这4对，等价于：

{0,0}：该对中的两个都是数字

{1,0}：该对中第一个是非数字，第二个是数字

{0,1}：该对中第一个是数字，第二个是非数字

{1,1}：该对中的两个都是非数字

显然，我们感兴趣的是中间的两对，因为这告诉我们字符串中数字与非数字的交界点。为此，将得到的由0/1组成的数组乘以一个由两个元素（2和1）组成的1行2列的数组。这样，公式转换为：

MMULT({2,0;0,0;0,1;2,0;0,0;0,0;0,0;0,1;2,1;2,0;0,0;0,0;0,0;0,1;2,0;0,0;0,1;2,0;0,0;0,1;2,1;2,1;2,1;2,1},{1;1})

得到：

{2;0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3}

数组中的0-3对应于上述四对组合。例如，得到3的唯一方式是1加2，而乘以{2,1}后得到由1和2组成的数组的对是{1,1}，其中的值都是非数字，因此3代表的都是非数字；值2来源于{2,1}乘以{1,0}，代表非数字后跟着一个数字；值1来源于{2,1}乘以{0,1}，代表数字后跟着一个非数字，等等。

因此，Arry2后生成的数组让我们可以知道字符串中的字符从数字变为非数字或者从非数字变为数字的位置。

3. 现在来看看单元格B1中的公式：

=IFERROR(0+MID(“α” & $A1& “α0″,1+SMALL(IF(Arry2=2,Arry1),COLUMNS($A:A)),SUM(SMALL(IF(ISNUMBER(MATCH(Arry2,{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})),””)

看看这里传递给MID函数的两个参数。要提取的字符串的起始位置参数start_num：

1+SMALL(IF(Arry2=2,Arry1),COLUMNS($A:A))

可以看到，我们基于Arry2等于2创建了一个数组，对应着由非数字字符和数字字符组成的对，即：

1+SMALL(IF({2;0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3}=2,{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}),COLUMNS($A:A))

转换为：

1+SMALL(IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}),COLUMNS($A:A))

转换为：

1+SMALL({1;FALSE;FALSE;4;FALSE;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FALSE;FALSE;15;FALSE;FALSE;18;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},COLUMNS($A:A))

可以看到，生成的数组中的数值1、4、10、15、18分别为指定字符串中每个数字的起始位置。在B1中，COLUMNS函数返回1，公式可转换为：

1+SMALL({1;FALSE;FALSE;4;FALSE;FALSE;FALSE;FALSE;FALSE;10;FALSE;FALSE;FALSE;FALSE;15;FALSE;FALSE;18;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE},1)

结果为（因为我们事先在A1中的字符串之前添加了一个字符）：

2

对于传递给MID函数的获取要提取的字符数的参数num_char：

SUM(SMALL(IF(ISNUMBER(MATCH(Arry2,{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})

我们对Arry2中的值1或2感兴趣，因为它们对应着字符串中的非数字/数字对。

要确定提取的每个子字符串的长度，需要计算每个连续的非数字/数字和数字/非数字的间隔之间的字符数，因为它们代表每组连续数字的开始和结束位置。将Arry2值代入后，上述公式转换为：

=SUM(SMALL(IF(ISNUMBER(MATCH({2;0;1;2;0;0;0;1;3;2;0;0;0;1;2;0;1;2;0;1;3;3;3;3},{1,2},0)),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})

转换为：

=SUM(SMALL(IF(ISNUMBER({2;#N/A;1;2;#N/A;#N/A;#N/A;1;#N/A;2;#N/A;#N/A;#N/A;1;2;#N/A;1;2;#N/A;1;#N/A;#N/A;#N/A;#N/A}),Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})

转换为：

=SUM(SMALL(IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},Arry1),2*COLUMNS($A:A)+{-1,0})*{-1,1})

代入Arry1的值：

=SUM(SMALL(IF({TRUE;FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24}),2*COLUMNS($A:A)+{-1,0})*{-1,1})

转换为：

=SUM(SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;8;FALSE;10;FALSE;FALSE;FALSE;14;15;FALSE;17;18;FALSE;20;FALSE;FALSE;FALSE;FALSE},2*COLUMNS($A:A)+{-1,0})*{-1,1})

上面生成的数组中的数值代表着字符串中从非数字到数字或者从数字到非数字的位置。

现在，需要指定SMALL函数的参数k，当我们向右拖拉公式时可以提取一对相应位置的数字。第一对是第1和第2个值，即1和3；第二对是第3和第4个值，即4和8；依此类推。然后，由每一对中第2个值减去第1个值得到想要的长度。因此，在B1中，公式可转换为：

=SUM(SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;8;FALSE;10;FALSE;FALSE;FALSE;14;15;FALSE;17;18;FALSE;20;FALSE;FALSE;FALSE;FALSE},2*1+{-1,0})*{-1,1})

转换为：

=SUM(SMALL({1;FALSE;3;4;FALSE;FALSE;FALSE;8;FALSE;10;FALSE;FALSE;FALSE;14;15;FALSE;17;18;FALSE;20;FALSE;FALSE;FALSE;FALSE},{1,2})*{-1,1})

转换为：

=SUM({1,3}*{-1,1})

转换为：

=SUM({-1,3})

得到：

2

我们将上面的中间结果代入单元格B1的公式：

=IFERROR(0+MID(“α”&$A1,2,2),””)

转换为：

=IFERROR(0+MID(“α81;8.75>@5279@4.=45>A?A;”,2,2),””)

转换为：

=IFERROR(0+”81″,””)

结果为：

81

值得一提的是，这个公式也适用于提取任何字母数混合的字符串中的数字。虽然平时从字符串中提取多个连续的数字的需求并不常见，但该技术仍然值得细细研究。