【原】excel數(shù)據(jù)提取技巧：從混合文本中提取數(shù)字的萬能公式

部落窩教育BLW 2020-08-01

展開全文

編按：哈嘍，大家好！有沒有能把任何文本中包含的所有數(shù)字都提取出來的公式？當(dāng)然是有的，今天就給大家?guī)硖崛?shù)字的萬能公式，不管數(shù)字在文本中的位置是否有規(guī)律，不管文本中數(shù)字有多少，它都能把數(shù)字提取出來。趕緊來看看吧！學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。

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

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

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

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

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

萬能公式：

{=-LOOKUP(9^9,-MIDB(A2,MIN(FINDB(LEFT(ROW($1:$11)-2,1),A2&-1/19)),ROW($1:$100)))}

公式詳細(xì)拆解如下：

①LEFT(ROW(1:11)-2,1)

ROW(1:11)很好理解，返回第1行到第11行的行號(hào)，也就是11個(gè)字符組成的集合A{1,2,3…11}，-2則變?yōu)?strong>字符集B{-1,0,1,2…9}。再通過LEFT提取字符集B左側(cè)的第一個(gè)字符，生成字符集C{"-",0,1,2,…9}，也就是符號(hào)和0-9這十個(gè)字符，所有數(shù)值，均由這11個(gè)字符構(gòu)成。

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

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

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

該公式中使用了A2&-1/19是為了確保字符集C{"-",0,1,2,…9}的每一個(gè)字符均在FIND的查找文本中出現(xiàn)，確保FIND的返回值不存在錯(cuò)誤值。片段②返回字符集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,…}中的最小值，它就是目標(biāo)數(shù)值在A2中的起始位置，即A2混合文本中，首次出現(xiàn)負(fù)號(hào)或阿拉伯?dāng)?shù)字的位置，即是目標(biāo)提取數(shù)值的起始位置。這就是為什么要求目標(biāo)數(shù)字的左側(cè)，不能有無關(guān)的阿拉伯?dāng)?shù)字或負(fù)號(hào)的原因。

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

這里使用MIDB，而不是MID，是為了對(duì)應(yīng)FINDB，通過字節(jié)位置截取部分文本。ROW($1:$100)返回有序數(shù)組{1-100}，作為MIDB函數(shù)的第三個(gè)參數(shù)——要提取的字節(jié)數(shù)，即分別提取1-100個(gè)字符。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。

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

⑤-LOOKUP(9^9,④）

LOOKUP查詢有三個(gè)特性：

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

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

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

由此，我們賦予查詢值一個(gè)極大數(shù)9^9，因?yàn)?span style="margin-top: 0px;margin-bottom: 0px;padding: 0px;">LOOKUP的特性1，所以查詢區(qū)域的最后一個(gè)非錯(cuò)誤值為最大值，即該值為返回值。LOOKUP的這幾個(gè)特性，完美地做到了忽略錯(cuò)誤值取最后一個(gè)有效值！

五、提取字符的萬能公式

用法：依次提取目標(biāo)單元格的全部數(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)}

公式簡(jiǎn)要拆解如下：

① ISNUMBER(--MID(A2,ROW($1:$100),1))*ROW($1:$100)

通過MID(A2,ROW($1:$100),1)逐一提取每一個(gè)字符，使用雙負(fù)號(hào)運(yùn)算，區(qū)分?jǐn)?shù)字和其它字符，再使用ISNUMBER函數(shù)判斷每一個(gè)字符是否為數(shù)字，返回一組邏輯值，最后*ROW($1:$100)使得數(shù)字返回其在A2混合文本中的位置，其他字符返回0。

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

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

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

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

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

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

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

19版萬能公式如下：

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