在上一篇文章中，小花講解了通過觀察混合文本特征，設定特定公式，完成資料提取的三種情景。于是，有些小花瓣悄悄跟小花說：小花老師，我笨，看不出資料特征，我又懶，不想分情景設定不同公式，有沒有那種霸王級萬能公式，啥混合文本咱都可以硬上弓？
答案自然是，有的！不過，還是要區分兩種情況。一種是提取數值，有正負之分大小之别，也有小數點；另一種是提取數字字元串，如電話号碼、身份證号碼等，這裡的數字沒有小數和負号，也沒大小之分。
這兩種情景的萬能公式分别該怎麼寫，又該怎麼了解呢？且聽小花細細道來。
四、提取數值的萬能公式
情景特征：除了目标數值，文本中不存在其他數字，否則容易産生幹擾。
萬能公式：
{=-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),""))}