編按：

哈嘍，大家好！前面我們分享了不用函數(shù)公式提取數(shù)字的5種方法。今天我們分享用簡單公式從混合文本中提取數(shù)字的方法。因為采用的公式很簡單，所以總體來說只適合數(shù)字在文本中的位置有一定規(guī)律的情況。如果想用公式提取沒有位置規(guī)律的數(shù)字，那就得看我們下一篇教程。

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

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

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

情景一：簡單不定長

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

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

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

解決思路：

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

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

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

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

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

情景二：特定符號引導

特殊符號引導混合文本的特征：

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

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

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

解決思路：

該情景雖然可以通過FIND函數(shù)鎖定特殊符號的初始位置，但卻因為其他單字節(jié)字符的存在，導致情景一中用2*LEN-LENB確認長度的方法無法使用，情景一公式在情景二中宣告失敗。

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

PS：數(shù)字99代表一個大于所有文本長度的字符數(shù)，不是固定值，可以根據(jù)實際情況修改。

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

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

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

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

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

解決思路：

該情景雖然可以通過LEFT或RIGHT函數(shù)從兩側(cè)提取數(shù)字，但同樣因存在其他單字節(jié)字符，無法使用2*LEN-LENB確認數(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è)，所以，目標數(shù)字總是所有純數(shù)字的最后一個，例如例子B2中，299.19是最后一個純數(shù)字，也是目標數(shù)字。所以，我們使用LOOKUP查詢一個極大數(shù)字9^9來獲取最后一個純數(shù)字。

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

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

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

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

在下篇文章中，小花將為大家介紹并詳細拆解提取數(shù)值萬能公式和提取數(shù)字字符串萬能公式，有興趣深入學習的小伙伴請務必吃透今日公式，通過點擊在看按鈕告訴小花你學會了，積累在看數(shù)，召喚新篇章。

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

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

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

相關(guān)推薦：

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

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

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

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