編按：

哈嘍，大家好！有沒有能把任何文本中包含的所有數(shù)字都提取出來的公式？當然是有的，今天就給大家?guī)硖崛?shù)字的萬能公式，不管數(shù)字在文本中的位置是否有規(guī)律，不管文本中數(shù)字有多少，它都能把數(shù)字提取出來。趕緊來看看吧！

在上一篇文章中，小花講解了通過觀察混合文本特征，設(shè)置特定公式，完成數(shù)據(jù)提取的三種情景。于是，有些小花瓣悄悄跟小花說：小花老師，我笨，看不出數(shù)據(jù)特征，我又懶，不想分情景設(shè)置不同公式，有沒有那種霸王級萬能公式，啥混合文本咱都可以硬上弓？

答案自然是，有的！不過，還是要區(qū)分兩種情況。一種是提取數(shù)值，有正負之分大小之別，也有小數(shù)點；另一種是提取數(shù)字字符串，如電話號碼、身份證號碼等，這里的數(shù)字沒有小數(shù)和負號，也沒大小之分。

這兩種情景的萬能公式分別該怎么寫，又該怎么理解呢？且聽小花細細道來。

四、提取數(shù)值的萬能公式

情景特征：除了目標數(shù)值，文本中不存在其他數(shù)字，否則容易產(chǎn)生干擾。

萬能公式：

{=-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則變?yōu)?b>字符集B{-1,0,1,2…9}。再通過LEFT提取字符集B左側(cè)的第一個字符，生成字符集C{"-",0,1,2,…9}，也就是符號和0-9這十個字符，所有數(shù)值，均由這11個字符構(gòu)成。

綜上，該部分的功能就是構(gòu)建阿拉伯數(shù)字全部字符，這些數(shù)字有助于我們鎖定位置，進而提取阿拉伯數(shù)值。

②FINDB(①,A2&-1/19)

FINDB是查找字符所在目標文本中的位置，它與FIND的差異是，它返回字節(jié)序號，即把漢字和中文符號視為2個字節(jié)。由此可知，A2單元格混合文本中，負號“-”出現(xiàn)的位置是5，而不是3。

該公式中使用了A2&-1/19是為了確保字符集C{"-",0,1,2,…9}的每一個字符均在FIND的查找文本中出現(xiàn)，確保FIND的返回值不存在錯誤值。片段②返回字符集C{"-",0,1,2,…9}在A2&-1/19出現(xiàn)的位置，即序數(shù)集D{5,13,10,6,…}。

③MIN(②)

MIN(②)取②的結(jié)果序數(shù)集D{5,13,10,6,…}中的最小值，它就是目標數(shù)值在A2中的起始位置，即A2混合文本中，首次出現(xiàn)負號或阿拉伯數(shù)字的位置，即是目標提取數(shù)值的起始位置。這就是為什么要求目標數(shù)字的左側(cè)，不能有無關(guān)的阿拉伯數(shù)字或負號的原因。

④-MIDB(A2,③,ROW($1:$100))

這里使用MIDB，而不是MID，是為了對應(yīng)FINDB，通過字節(jié)位置截取部分文本。ROW($1:$100)返回有序數(shù)組{1-100}，作為MIDB函數(shù)的第三個參數(shù)——要提取的字節(jié)數(shù)，即分別提取1-100個字符。

于是，MIDB函數(shù)的功能就是從③確定的起始位置開始，分別從A2單元格文本中截取長度為1-100個字節(jié)的100個不等長字符串E{"-","-2","-29","-299",…"-299.19"}。而-MIDB則是將不等長字符串執(zhí)行減法運算，使得非數(shù)值數(shù)據(jù)因無法運算而報錯為#VALUE!，進而將不等長字符串E轉(zhuǎn)化為純數(shù)字和錯誤值#VALUE!組成的新常量數(shù)組F{#VALUE!;2;29;299;299;299.1;299.19;…;299.19}

⑤-LOOKUP(9^9,④）

LOOKUP查詢有三個特性：

1．默認查詢區(qū)域是升序的，即越往后值越大。

2．返回值應(yīng)小于且最接近于查詢值。

3．忽略查詢區(qū)域中的錯誤值。

由此，我們賦予查詢值一個極大數(shù)9^9，因為LOOKUP的特性1，所以查詢區(qū)域的最后一個非錯誤值為最大值，即該值為返回值。LOOKUP的這幾個特性，完美地做到了忽略錯誤值取最后一個有效值！

五、提取字符的萬能公式

用法：依次提取目標單元格的全部數(shù)值并合并。

萬能公式：

{=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)逐一提取每一個字符，使用雙負號運算，區(qū)分數(shù)字和其它字符，再使用ISNUMBER函數(shù)判斷每一個字符是否為數(shù)字，返回一組邏輯值，最后*ROW($1:$100)使得數(shù)字返回其在A2混合文本中的位置，其他字符返回0。

② LARGE(①,ROW($1:$100))

通過LARGE函數(shù)，將①中的字符位置值集合從大到小重新排序。由于數(shù)字在文本中的位置總是大于0，且數(shù)字越靠后，位置值越靠前。而其他字符總是小于0的。這里的重點是將所有的0值置后，同時將所有數(shù)字位置值倒排。

③ MID(0&A2,②+1,1)

MID根據(jù)②的位置值+1從0&A2中逐一取數(shù)。由于非數(shù)字的位置值為0，所有非數(shù)字返回值均取首位0，其余數(shù)字不受影響。由于②的數(shù)字位置值是顛倒的，所以，此時提取出的數(shù)字前后也是顛倒的。

④ SUM(③*10^ROW($1:$100)/10))

前三步得到了A2單元格中的所有數(shù)字和一串代表非數(shù)字位置的0組成的有序數(shù)組，此時要完成最終的提取，還需要將數(shù)字正序排列、去除0值并將其合并。這些通通交由*10^ROW($1:$100)/10完成，它通過構(gòu)建一個多位數(shù)來將各個數(shù)字順序擺放，最終將代表文本的有效數(shù)位前的0值省略，其余數(shù)字按次序從個位開始向左排列。最終的多位數(shù)即數(shù)字提取結(jié)果。

其實，提取數(shù)字字符串的問題，19年以后版本有了一個很簡單又不燒腦的解決方案––通過CONCAT直接連接就行了。

19版萬能公式如下：

{=CONCAT(IFERROR(--MID($A2,ROW($1:$100),1),""))}

公式簡要說明：

1.使用MID和ROW組合，將每一個字符逐一提取出來。

2.通過雙負號區(qū)分數(shù)字和非數(shù)字，非數(shù)字將報錯。

3.IFERROR將非數(shù)字錯誤值轉(zhuǎn)化為空。

4.使用CONCAT函數(shù)將所有數(shù)字合并。

曾經(jīng)有位朋友跟小花說

『文本函數(shù)才是函數(shù)界的黑洞，永遠無法看透』

今天的你是否已有同感？

莫怕莫怕，洗把臉重頭再看一遍。

『寶劍鋒從磨礪出，梅花香自苦寒來！』

本文配套的練習課件請加入QQ群：1043683754下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

提取數(shù)字（技巧篇）《Excel數(shù)字提取技巧:從無規(guī)律文本中提取手機號的5種方法》

提取數(shù)字（場景篇）《Excel數(shù)字提取技巧:用簡單公式從混合文本中提取數(shù)字的3種情景》

提取身份證號碼（上篇）《2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏）-上篇》

提取身份證號碼（下篇）《2019年全網(wǎng)最全—excel提取身份證信息合集?。ńㄗh收藏）-下篇》