excel數據提取技巧：從混合文本中提取數字的萬能公式
編按：哈嘍，大家好！有沒有能把任何文本中包含的所有數字都提取出來的公式？當然是有的，今天就給大家帶來提取數字的萬能公式，不管數字在文本中的位置是否有規律，不管文本中數字有多少，它都能把數字提取出來。趕緊來看看吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。
在上一篇文章中，小花講解了通過觀察混合文本特徵，設置特定公式，完成數據提取的三種情景。於是，有些小花瓣悄悄跟小花説：小花老師，我笨，看不出數據特徵，我又懶，不想分情景設置不同公式，有沒有那種霸王級萬能公式，啥混合文本咱都可以硬上弓？
答案自然是，有的！不過，還是要區分兩種情況。一種是提取數值，有正負之分大小之別，也有小數點；另一種是提取數字字符串，如電話號碼、身份證號碼等，這裏的數字沒有小數和負號，也沒大小之分。
這兩種情景的萬能公式分別該怎麼寫，又該怎麼理解呢？且聽小花細細道來。
四、提取數值的萬能公式
情景特徵：除了目標數值，文本中不存在其他數字，否則容易產生干擾。
萬能公式：
{=-LOOKUP(9^9,-MIDB(A2,MIN(FINDB(LEFT(ROW($1:$11)-2,1),A2&-1/19)),ROW($1:$100)))}
公式詳細拆解如下：
①LEFT(ROW(1:11)-2,1)
ROW(1:11)很好理解，返回第1行到第11行的行號，也就是11個字符組成的集合A{1,2,3…11}，-2則變為字符集B{-1,0,1,2…9}。再通過LEFT提取字符集B左側的第一個字符，生成字符集C{"-",0,1,2,…9}，也就是符號和0-9這十個字符，所有數值，均由這11個字符構成。
綜上，該部分的功能就是構建阿拉伯數字全部字符，這些數字有助於我們鎖定位置，進而提取阿拉伯數值。
②FINDB(①,A2&-1/19)
FINDB是查找字符所在目標文本中的位置，它與FIND的差異是，它返回字節序號，即把漢字和中文符號視為2個字節。由此可知，A2單元格混合文本中，負號“-”出現的位置是5，而不是3。
該公式中使用了A2&-1/19是為了確保字符集C{"-",0,1,2,…9}的每一個字符均在FIND的查找文本中出現，確保FIND的返回值不存在錯誤值。片段②返回字符集C{"-",0,1,2,…9}在A2&-1/19出現的位置，即序數集D{5,13,10,6,…}。
③MIN(②)
MIN(②)取②的結果序數集D{5,13,10,6,…}中的最小值，它就是目標數值在A2中的起始位置，即A2混合文本中，首次出現負號或阿拉伯數字的位置，即是目標提取數值的起始位置。這就是為什麼要求目標數字的左側，不能有無關的阿拉伯數字或負號的原因。
④-MIDB(A2,③,ROW($1:$100))
這裏使用MIDB，而不是MID，是為了對應FINDB，通過字節位置截取部分文本。ROW($1:$100)返回有序數組{1-100}，作為MIDB函數的第三個參數——要提取的字節數，即分別提取1-100個字符。學習更多技巧，請收藏關注部落窩教育excel圖文教程。
於是，MIDB函數的功能就是從③確定的起始位置開始，分別從A2單元格文本中截取長度為1-100個字節的100個不等長字符串E{"-","-2","-29","-299",…"-299.19"}。而-MIDB則是將不等長字符串執行減法運算，使得非數值數據因無法運算而報錯為#VALUE!，進而將不等長字符串E轉化為純數字和錯誤值#VALUE!組成的新常量數組F{#VALUE!;2;29;299;299;299.1;299.19;…;299.19}
⑤-LOOKUP(9^9,④）
LOOKUP查詢有三個特性：
1．默認查詢區域是升序的，即越往後值越大。
2．返回值應小於且最接近於查詢值。
3．忽略查詢區域中的錯誤值。
由此，我們賦予查詢值一個極大數9^9，因為LOOKUP的特性1，所以查詢區域的最後一個非錯誤值為最大值，即該值為返回值。LOOKUP的這幾個特性，完美地做到了忽略錯誤值取最後一個有效值！
五、提取字符的萬能公式
用法：依次提取目標單元格的全部數值併合並。
萬能公式：
{=SUM(MID(0&A2,LARGE(ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100),ROW($1:$100))+1,1)*10^ROW($1:$100)/10)}
公式簡要拆解如下：
① ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100)
通過MID(A2,ROW($1:$100),1)逐一提取每一個字符，使用雙負號運算，區分數字和其它字符，再使用ISNUMBER函數判斷每一個字符是否為數字，返回一組邏輯值，最後*ROW($1:$100)使得數字返回其在A2混合文本中的位置，其他字符返回0。
② LARGE(①,ROW($1:$100))
通過LARGE函數，將①中的字符位置值集合從大到小重新排序。由於數字在文本中的位置總是大於0，且數字越靠後，位置值越靠前。而其他字符總是小於0的。這裏的重點是將所有的0值置後，同時將所有數字位置值倒排。
③ MID(0&A2,②+1,1)
MID根據②的位置值+1從0&A2中逐一取數。由於非數字的位置值為0，所有非數字返回值均取首位0，其餘數字不受影響。由於②的數字位置值是顛倒的，所以，此時提取出的數字前後也是顛倒的。
④ SUM(③*10^ROW($1:$100)/10))
前三步得到了A2單元格中的所有數字和一串代表非數字位置的0組成的有序數組，此時要完成最終的提取，還需要將數字正序排列、去除0值並將其合併。這些通通交由*10^ROW($1:$100)/10完成，它通過構建一個多位數來將各個數字順序擺放，最終將代表文本的有效數位前的0值省略，其餘數字按次序從個位開始向左排列。最終的多位數即數字提取結果。
其實，提取數字字符串的問題，19年以後版本有了一個很簡單又不燒腦的解決方案––通過CONCAT直接連接就行了。
19版萬能公式如下：
{=CONCAT(IFERROR(--MID($A2,ROW($1:$100),1),""))}
公式簡要説明：
1.使用MID和ROW組合，將每一個字符逐一提取出來。
2.通過雙負號區分數字和非數字，非數字將報錯。
3.IFERROR將非數字錯誤值轉化為空。
4.使用CONCAT函數將所有數字合併。
以上，分享結束。學習更多技巧，請收藏關注部落窩教育excel圖文教程。
＊＊＊＊部落窩教育-excel數字提取萬能公式＊＊＊＊
原創：小花/部落窩教育（未經同意，請勿轉載）
更多教程：部落窩教育
微信公眾號：exceljiaocheng，+v：blwjymx2