前兩篇文章分别講解了提取位于字元串開頭和末尾的數字的公式技術，本文研究從字元串中提取所有數字的技術：
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
值得一提的是，這個公式也适用于提取任何字母數混合的字元串中的數字。雖然平時從字元串中提取多個連續的數字的需求并不常見，但該技術仍然值得細細研究。
妙哉，真巧奪天工也！
注：本技巧整理自excelxor.com，有興趣的朋友可以研閱原文，特别是原文後面的評論。