編按：哈嘍，大家好！前面我們分享了不用函數(shù)公式提取數(shù)字的5種方法。今天我們分享用簡單公式從混合文本中提取數(shù)字的方法。因?yàn)椴捎玫墓胶芎唵?，所以總體來說只適合數(shù)字在文本中的位置有一定規(guī)律的情況。如果想用公式提取沒有位置規(guī)律的數(shù)字，那就得看我們下一篇教程。學(xué)習(xí)更多技巧，請收藏關(guān)注部落窩教育excel圖文教程。

從理論上來說，我們應(yīng)當(dāng)避免將數(shù)字和文字填寫在同一個單元格中，從而產(chǎn)生混合文本，影響進(jìn)一步的數(shù)據(jù)處理和分析。但理想很豐滿，現(xiàn)實(shí)很骨感，由于慣例、系統(tǒng)設(shè)定或人員素質(zhì)等諸多原因，混合文本不可避免。于是，混合文本提取數(shù)字，成了很多Excel用戶必須面對的問題。

解決這一問題的思路有很多，函數(shù)是最主要手段之一。而如何設(shè)置函數(shù)公式，往往取決于混合文本的特征；觀察，是解決問題的最快路徑。下面，小花將和各位花瓣一起，邊觀察，邊解決。

PS：可由LEFT, RIGHT或MID直接截取的簡單問題，此處不再贅述。

情景一：簡單不定長

簡單不定長混合文本的特征：

1.不含英文及其他字符。

2.數(shù)字統(tǒng)一位于文本最左側(cè)、最右側(cè)或中間固定起始位置。

解決思路：數(shù)字初始位置固定，可以直接用LEFT、?RIGHT或MID提取，無需確認(rèn)起始位置。唯一需要計(jì)算的參數(shù)就是文本長度。這里由于混合文本不含單字節(jié)字符(英文字符或半角符號)，我們可以使用LEN和LENB來確定數(shù)字長度。其中，LEN計(jì)算總字符數(shù)，LENB計(jì)算總字節(jié)數(shù)，由于1個漢字=1個字符=2個字節(jié)，1個單字節(jié)字符=1個字符=1個字節(jié)，于是我們可以用2*LEN-LENB來計(jì)算數(shù)字的長度，從而完成提取。

PS：2*LEN-LENB確定數(shù)字長度的數(shù)學(xué)邏輯類似雞兔同籠，小花瓣們可以參照理解。

左側(cè)公式：=LEFT(D2,2*LEN(D2)-LENB(D2))

右側(cè)公式：=RIGHT(A2,2*LEN(A2)-LENB(A2))

中間公式：=MID(G2,3,2*LEN(G2)-LENB(G2))

情景二：特定符號引導(dǎo)

特殊符號引導(dǎo)混合文本的特征：

1.數(shù)字位置不固定。

2.數(shù)字長度也不固定。

3.數(shù)字有特殊字符引導(dǎo)，且可能存在其他單字節(jié)字符。

解決思路：該情景雖然可以通過FIND函數(shù)鎖定特殊符號的初始位置，但卻因?yàn)槠渌麊巫止?jié)字符的存在，導(dǎo)致情景一中用2*LEN-LENB確認(rèn)長度的方法無法使用，情景一公式在情景二中宣告失敗。

正確的思路是，使用SUBSTITUTE函數(shù)將指定符號替換為連續(xù)空格字符串(通過REPT函數(shù)構(gòu)建)，從而使數(shù)字處在足夠多個的連續(xù)空格之間。再通過MID函數(shù)截取“空格+數(shù)字+空格”這樣的字符串，最后使用TRIM去除多余空值，實(shí)現(xiàn)對數(shù)字的提取。

PS：數(shù)字99代表一個大于所有文本長度的字符數(shù)，不是固定值，可以根據(jù)實(shí)際情況修改。學(xué)習(xí)更多技巧，請收藏關(guān)注部落窩教育excel圖文教程。

情景三：含半角符兩側(cè)不定長

含半角符兩側(cè)不定長混合文本的特征：

1.數(shù)字位置在文本兩側(cè)。

2.數(shù)字長度不固定。

3.混合文本中含英文字母、半角符號等單字節(jié)字符。

解決思路：該情景雖然可以通過LEFT或RIGHT函數(shù)從兩側(cè)提取數(shù)字，但同樣因存在其他單字節(jié)字符，無法使用2*LEN-LENB確認(rèn)數(shù)字長度。同時，因?yàn)闊o固定引導(dǎo)符號，使用長空格的設(shè)想也就此落空。

在這種情況下，我們可以使用數(shù)組的方法，依次提取每一個可能的結(jié)果值。如案例中的B2單元格，我們依次從“299.19公斤”中，從左提取1到100個字符，生成“2,29,299,299.,299.1,299.19,299.19公,299.19公斤,299.19公斤,299.19公斤......”等100個不同長度的字符串。

由于數(shù)字總在混合文本兩側(cè)，所以，目標(biāo)數(shù)字總是所有純數(shù)字的最后一個，例如例子B2中，299.19是最后一個純數(shù)字，也是目標(biāo)數(shù)字。所以，我們使用LOOKUP查詢一個極大數(shù)字9^9來獲取最后一個純數(shù)字。

PS：LEFT前的負(fù)號用于將文本型數(shù)字轉(zhuǎn)化為數(shù)字型數(shù)字，而LOOKUP前的數(shù)字則用于恢復(fù)數(shù)值原來的正負(fù)性。

如果案例中的數(shù)字均為正數(shù)，我們還可以使用MAX法來解決問題。這是因?yàn)椋?jīng)過LEFT提取后的一串不等長字符中，由于純數(shù)字都是正數(shù)，所以目標(biāo)數(shù)字將同時滿足最長、最后且最大的特定，這一點(diǎn)小花瓣們可以通過公式求值進(jìn)行驗(yàn)證。所以，我們可以通過求最大值來鎖定目標(biāo)值。特別提醒，該公式為數(shù)組公式，輸入后需按【Ctrl+Shift+Enter】才能正確運(yùn)算。

這里需要注意的是，由于MAX函數(shù)不具備LOOKUP那樣剔除錯誤值的能力，所以我們需要使用IFERROR函數(shù)來賦予錯誤值（經(jīng)過雙負(fù)號轉(zhuǎn)換后，文本均顯示錯誤#VALUE!）一個足夠小的數(shù)字，從而不會影響MAX鎖定最大正數(shù)。這里我們可以明顯的看到B6單元格，當(dāng)目標(biāo)數(shù)字為負(fù)值時，公式出錯。這就是MAX法相較于LOOKUP的明顯劣勢，因此情景三，還是推薦使用LOOKUP法提取數(shù)字。

以上，就是混合文本提取函數(shù)公式的三種進(jìn)階情景應(yīng)用。其中的公式均只能在特定條件下生效，缺點(diǎn)明顯，缺乏普適性，但簡單高效，也較易理解，在觀察到對應(yīng)特征時，小花推薦使用對應(yīng)公式。

在下篇文章中，小花將為大家介紹并詳細(xì)拆解提取數(shù)值萬能公式和提取數(shù)字字符串萬能公式，有興趣深入學(xué)習(xí)的小伙伴請務(wù)必吃透今日公式。學(xué)習(xí)更多技巧，請收藏關(guān)注部落窩教育excel圖文教程。

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

＊＊＊＊部落窩教育-excel混合文本提取數(shù)字＊＊＊＊

原創(chuàng)：小花/部落窩教育（未經(jīng)同意，請勿轉(zhuǎn)載）

更多教程：部落窩教育（http://www.itblw.com/）

微信公眾號：exceljiaocheng，+v：blwjymx2

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

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)

相關(guān)推薦：

提取手機(jī)號碼《3分鐘，帶你看懂提取手機(jī)號碼的經(jīng)典公式套路》

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

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

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