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”)⑴))

名称：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)+{⑴,0})*{⑴,1})),””)

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

原理解析

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

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

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

上述公式转换为：

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

结果为：

{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⑼范围的整数的ASCⅡ编码从48到57，小数点是46。因此，这种情况下，如果首先从字符串中排除与ASCⅡ编码47相对应的任何字符（“/”），那末可以肯定字符串中ASCⅡ编码在46⑸7范围内的任何字符要末是数字要末是小数点。

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

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

（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}：该对中第一个是非数字，第2个是数字

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

{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⑶对应于上述4对组合。举例来看，得到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)+{⑴,0})*{⑴,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)+{⑴,0})*{⑴,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)+{⑴,0})*{⑴,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)+{⑴,0})*{⑴,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)+{⑴,0})*{⑴,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)+{⑴,0})*{⑴,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)+{⑴,0})*{⑴,1})

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

现在，需要指定SMALL函数的参数k，当我们向右拖拉公式时可以提取一对相应位置的数字。第一对是第1和第2个值，即1和3；第2对是第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+{⑴,0})*{⑴,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})

转换为：

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

转换为：

=SUM({⑴,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

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