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

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