本文研究從字符串中提取所有數(shù)字的技術(shù)：

1. 字符串由數(shù)字、字母和特殊字符組成

2. 數(shù)字在字符串的任意地方

3. 字符串中的小數(shù)也一樣提取

3. 想要的結(jié)果是將所有數(shù)字返回獨(dú)立的單元格

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

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

返回：

單元格B1：81

單元格C1：8.75

單元格D1：5279

單元格E1：4

單元格F1：45

解決方案

首先，確保活動(dòng)單元格處于工作表行1中，然后定義下面兩個(gè)名稱。

名稱：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中輸入數(shù)組公式：

=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})),””)

向右拖放直至出現(xiàn)空單元格為止。

原理解析

1. 先看看這兩個(gè)定義的名稱。對(duì)于Arry1：

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

生成由整數(shù)構(gòu)成的數(shù)組。注意，在單元格A1的字符串前面添加了一個(gè)非數(shù)字字符“α”，在末尾添加了一個(gè)非數(shù)字字符和一個(gè)數(shù)字“α0”。為什么這樣處理？具體原因在后文詳述。

上述公式轉(zhuǎn)換為：

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

結(jié)果為：

{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. 對(duì)于Arry2：

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

（1）公式通過(guò)引用ASCⅡ字符代碼來(lái)測(cè)試單元格A1里的數(shù)字。

（2）要識(shí)別數(shù)字子字符串，必須找到字符串里兩個(gè)不同的位置：一個(gè)對(duì)應(yīng)著數(shù)字的起始位置，另一個(gè)對(duì)應(yīng)著數(shù)字的結(jié)束位置。

（3）公式中的第一部分將給MID函數(shù)提供參數(shù)start_num，在生成的結(jié)果之間的減法提供相應(yīng)的參數(shù)num_chars。

（4）對(duì)于0-9范圍的整數(shù)的ASCⅡ編碼從48到57，小數(shù)點(diǎn)是46。因此，如果首先從字符串中排除與ASCⅡ編碼47相對(duì)應(yīng)的任何字符（“/”），那么可以確定字符串中ASCⅡ編碼在46-57范圍內(nèi)的任何字符要么是數(shù)字要么是小數(shù)點(diǎn)。

（5）使用字符的ASCⅡ編碼減51.5，判斷其結(jié)果的絕對(duì)值，如果小于或等于6，則可以判斷該字符是數(shù)字或小數(shù)點(diǎn)。（這里運(yùn)用的技巧等價(jià)于通常要使用的兩個(gè)單獨(dú)的條件判斷，即一個(gè)來(lái)比較ASCⅡ編碼大于45，另一個(gè)來(lái)比較ASCⅡ編碼小于58。）

（6）注意，為了發(fā)現(xiàn)數(shù)字的開始位置和結(jié)束位置，這里查找字符串里的兩對(duì)字符：一對(duì)中的第一個(gè)字符是非數(shù)字字符而第二個(gè)是數(shù)字字符（提供數(shù)字字符串的開始），另一對(duì)中的第一個(gè)字符是數(shù)字字符而第二個(gè)是非數(shù)字字符（提供數(shù)字字符串的結(jié)尾）。

（7）當(dāng)然，如果字符串中的第一個(gè)或最后一個(gè)字符與上述標(biāo)準(zhǔn)相符，那么需要確保有一些字符在它們的前面或后面，這就是我們?cè)贏1的開頭和結(jié)尾連接合適的字符串的原因。于是，就有了你所看見(jiàn)的”α”&和&”α0″。

這樣，Arry2公式轉(zhuǎn)換為：

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})

轉(zhuǎn)換為：

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})

接下來(lái)是值得關(guān)注的技術(shù)之一。因?yàn)橄Ｍ麖淖址�串里考慮成對(duì)的字符，所以需要將字符串里位置1中的字符和位置2中的字符比較、位置2中的字符和位置3中的字符比較，依此類推。為了實(shí)現(xiàn)這個(gè)目的，需要生成傳遞給MID函數(shù)作為參數(shù)start_num的數(shù)組：{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}，是一個(gè)24行1列的數(shù)組，將其與一個(gè)1行2列的數(shù)組{0,1}相加，結(jié)果是一個(gè)24行2列的數(shù)組：

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})

轉(zhuǎn)換為：

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})

轉(zhuǎn)換為：

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})

轉(zhuǎn)換為：

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})

轉(zhuǎn)換為：

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})

下面來(lái)看看現(xiàn)在得到的這個(gè)數(shù)組中的值代表的意思，我們高亮顯示4組數(shù)字為例：

{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函數(shù)中得到的字符數(shù)組：

{“α”,”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個(gè)高亮顯示的對(duì)是{0,1}，相應(yīng)的字符是{“1”,”;”}，因?yàn)椤?”是數(shù)字而“;”不是。

（2）第2個(gè)高亮顯示的對(duì)是{1,1}，相應(yīng)的字符是{“>”,”@”}，因?yàn)椤?”和”@”都不是數(shù)字。

（3）第3個(gè)高亮顯示的對(duì)是{1,0}，相應(yīng)的字符是{“@”,”4″}，由非數(shù)字和數(shù)字組成。

（4）第4個(gè)高亮顯示的對(duì)是{0,0}，相應(yīng)的字符是{“4″,”5”}，都是數(shù)字。

現(xiàn)在需要一種方法來(lái)區(qū)分這4對(duì)，等價(jià)于：

{0,0}：該對(duì)中的兩個(gè)都是數(shù)字

{1,0}：該對(duì)中第一個(gè)是非數(shù)字，第二個(gè)是數(shù)字

{0,1}：該對(duì)中第一個(gè)是數(shù)字，第二個(gè)是非數(shù)字

{1,1}：該對(duì)中的兩個(gè)都是非數(shù)字

顯然，我們感興趣的是中間的兩對(duì)，因?yàn)檫@告訴我們字符串中數(shù)字與非數(shù)字的交界點(diǎn)。為此，將得到的由0/1組成的數(shù)組乘以一個(gè)由兩個(gè)元素（2和1）組成的1行2列的數(shù)組。這樣，公式轉(zhuǎn)換為：

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}

數(shù)組中的0-3對(duì)應(yīng)于上述四對(duì)組合。例如，得到3的唯一方式是1加2，而乘以{2,1}后得到由1和2組成的數(shù)組的對(duì)是{1,1}，其中的值都是非數(shù)字，因此3代表的都是非數(shù)字；值2來(lái)源于{2,1}乘以{1,0}，代表非數(shù)字后跟著一個(gè)數(shù)字；值1來(lái)源于{2,1}乘以{0,1}，代表數(shù)字后跟著一個(gè)非數(shù)字，等等。

因此，Arry2后生成的數(shù)組讓我們可以知道字符串中的字符從數(shù)字變?yōu)榉菙?shù)字或者從非數(shù)字變?yōu)閿?shù)字的位置。

3. 現(xiàn)在來(lái)看看單元格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函數(shù)的兩個(gè)參數(shù)。要提取的字符串的起始位置參數(shù)start_num：

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

可以看到，我們基于Arry2等于2創(chuàng)建了一個(gè)數(shù)組，對(duì)應(yīng)著由非數(shù)字字符和數(shù)字字符組成的對(duì)，即：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

可以看到，生成的數(shù)組中的數(shù)值1、4、10、15、18分別為指定字符串中每個(gè)數(shù)字的起始位置。在B1中，COLUMNS函數(shù)返回1，公式可轉(zhuǎn)換為：

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)

結(jié)果為（因?yàn)槲覀兪孪仍贏1中的字符串之前添加了一個(gè)字符）：

2

對(duì)于傳遞給MID函數(shù)的獲取要提取的字符數(shù)的參數(shù)num_char：

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

我們對(duì)Arry2中的值1或2感興趣，因?yàn)樗鼈儗?duì)應(yīng)著字符串中的非數(shù)字/數(shù)字對(duì)。

要確定提取的每個(gè)子字符串的長(zhǎng)度，需要計(jì)算每個(gè)連續(xù)的非數(shù)字/數(shù)字和數(shù)字/非數(shù)字的間隔之間的字符數(shù)，因?yàn)樗鼈兇�表每組連續(xù)數(shù)字的開始和結(jié)束位置。將Arry2值代入后，上述公式轉(zhuǎn)換為：

=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})

轉(zhuǎn)換為：

=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})

轉(zhuǎn)換為：

=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})

轉(zhuǎn)換為：

=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})

上面生成的數(shù)組中的數(shù)值代表著字符串中從非數(shù)字到數(shù)字或者從數(shù)字到非數(shù)字的位置。

現(xiàn)在，需要指定SMALL函數(shù)的參數(shù)k，當(dāng)我們向右拖拉公式時(shí)可以提取一對(duì)相應(yīng)位置的數(shù)字。第一對(duì)是第1和第2個(gè)值，即1和3；第二對(duì)是第3和第4個(gè)值，即4和8；依此類推。然后，由每一對(duì)中第2個(gè)值減去第1個(gè)值得到想要的長(zhǎng)度。因此，在B1中，公式可轉(zhuǎn)換為：

=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})

轉(zhuǎn)換為：

=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})

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

=SUM({-1,3})

得到：

2

我們將上面的中間結(jié)果代入單元格B1的公式：

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

轉(zhuǎn)換為：

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

轉(zhuǎn)換為：

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

結(jié)果為：

81

值得一提的是，這個(gè)公式也適用于提取任何字母數(shù)混合的字符串中的數(shù)字。雖然平時(shí)從字符串中提取多個(gè)連續(xù)的數(shù)字的需求并不常見(jiàn)，但該技術(shù)仍然值得細(xì)細(xì)研究。