編按：哈嘍，大家好！前面我們分享了不用函數(shù)公式提取數(shù)字的5種方法。今天我們分享用簡(jiǎn)單公式從混合文本中提取數(shù)字的方法。因?yàn)椴捎玫墓胶芎?jiǎn)單，所以總體來說只適合數(shù)字在文本中的位置有一定規(guī)律的情況。如果想用公式提取沒有位置規(guī)律的數(shù)字，那就得看我們下一篇教程。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。
從理論上來說，我們應(yīng)當(dāng)避免將數(shù)字和文字填寫在同一個(gè)單元格中，從而產(chǎn)生混合文本，影響進(jìn)一步的數(shù)據(jù)處理和分析。但理想很豐滿，現(xiàn)實(shí)很骨感，由于慣例、系統(tǒng)設(shè)定或人員素質(zhì)等諸多原因，混合文本不可避免。于是，混合文本提取數(shù)字，成了很多Excel用戶必須面對(duì)的問題。
解決這一問題的思路有很多，函數(shù)是最主要手段之一。而如何設(shè)置函數(shù)公式，往往取決于混合文本的特征；觀察，是解決問題的最快路徑。下面，小花將和各位花瓣一起，邊觀察，邊解決。
PS：可由LEFT, RIGHT或MID直接截取的簡(jiǎn)單問題，此處不再贅述。
情景一：簡(jiǎn)單不定長(zhǎng)
簡(jiǎn)單不定長(zhǎng)混合文本的特征：
1.不含英文及其他字符。
2.數(shù)字統(tǒng)一位于文本最左側(cè)、最右側(cè)或中間固定起始位置。
解決思路：數(shù)字初始位置固定，可以直接用LEFT、 RIGHT或MID提取，無需確認(rèn)起始位置。唯一需要計(jì)算的參數(shù)就是文本長(zhǎng)度。這里由于混合文本不含單字節(jié)字符(英文字符或半角符號(hào))，我們可以使用LEN和LENB來確定數(shù)字長(zhǎng)度。其中，LEN計(jì)算總字符數(shù)，LENB計(jì)算總字節(jié)數(shù)，由于1個(gè)漢字=1個(gè)字符=2個(gè)字節(jié)，1個(gè)單字節(jié)字符=1個(gè)字符=1個(gè)字節(jié)，于是我們可以用2*LEN-LENB來計(jì)算數(shù)字的長(zhǎng)度，從而完成提取。
PS：2*LEN-LENB確定數(shù)字長(zhǎng)度的數(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))
情景二：特定符號(hào)引導(dǎo)
特殊符號(hào)引導(dǎo)混合文本的特征：
1.數(shù)字位置不固定。
2.數(shù)字長(zhǎng)度也不固定。
3.數(shù)字有特殊字符引導(dǎo)，且可能存在其他單字節(jié)字符。
解決思路：該情景雖然可以通過FIND函數(shù)鎖定特殊符號(hào)的初始位置，但卻因?yàn)槠渌麊巫止?jié)字符的存在，導(dǎo)致情景一中用2*LEN-LENB確認(rèn)長(zhǎng)度的方法無法使用，情景一公式在情景二中宣告失敗。
正確的思路是，使用SUBSTITUTE函數(shù)將指定符號(hào)替換為連續(xù)空格字符串(通過REPT函數(shù)構(gòu)建)，從而使數(shù)字處在足夠多個(gè)的連續(xù)空格之間。再通過MID函數(shù)截取“空格+數(shù)字+空格”這樣的字符串，最后使用TRIM去除多余空值，實(shí)現(xiàn)對(duì)數(shù)字的提取。
PS：數(shù)字99代表一個(gè)大于所有文本長(zhǎng)度的字符數(shù)，不是固定值，可以根據(jù)實(shí)際情況修改。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。
情景三：含半角符兩側(cè)不定長(zhǎng)
含半角符兩側(cè)不定長(zhǎng)混合文本的特征：
1.數(shù)字位置在文本兩側(cè)。
2.數(shù)字長(zhǎng)度不固定。
3.混合文本中含英文字母、半角符號(hào)等單字節(jié)字符。
解決思路：該情景雖然可以通過LEFT或RIGHT函數(shù)從兩側(cè)提取數(shù)字，但同樣因存在其他單字節(jié)字符，無法使用2*LEN-LENB確認(rèn)數(shù)字長(zhǎng)度。同時(shí)，因?yàn)闊o固定引導(dǎo)符號(hào)，使用長(zhǎng)空格的設(shè)想也就此落空。
在這種情況下，我們可以使用數(shù)組的方法，依次提取每一個(gè)可能的結(jié)果值。如案例中的B2單元格，我們依次從“299.19公斤”中，從左提取1到100個(gè)字符，生成“2,29,299,299.,299.1,299.19,299.19公,299.19公斤,299.19公斤,299.19公斤......”等100個(gè)不同長(zhǎng)度的字符串。
由于數(shù)字總在混合文本兩側(cè)，所以，目標(biāo)數(shù)字總是所有純數(shù)字的最后一個(gè)，例如例子B2中，299.19是最后一個(gè)純數(shù)字，也是目標(biāo)數(shù)字。所以，我們使用LOOKUP查詢一個(gè)極大數(shù)字9^9來獲取最后一個(gè)純數(shù)字。
PS：LEFT前的負(fù)號(hào)用于將文本型數(shù)字轉(zhuǎn)化為數(shù)字型數(shù)字，而LOOKUP前的數(shù)字則用于恢復(fù)數(shù)值原來的正負(fù)性。
如果案例中的數(shù)字均為正數(shù)，我們還可以使用MAX法來解決問題。這是因?yàn)椋?jīng)過LEFT提取后的一串不等長(zhǎng)字符中，由于純數(shù)字都是正數(shù)，所以目標(biāo)數(shù)字將同時(shí)滿足最長(zhǎng)、最后且最大的特定，這一點(diǎn)小花瓣們可以通過公式求值進(jìn)行驗(yàn)證。所以，我們可以通過求最大值來鎖定目標(biāo)值。特別提醒，該公式為數(shù)組公式，輸入后需按【Ctrl+Shift+Enter】才能正確運(yùn)算。
這里需要注意的是，由于MAX函數(shù)不具備LOOKUP那樣剔除錯(cuò)誤值的能力，所以我們需要使用IFERROR函數(shù)來賦予錯(cuò)誤值（經(jīng)過雙負(fù)號(hào)轉(zhuǎn)換后，文本均顯示錯(cuò)誤#VALUE!）一個(gè)足夠小的數(shù)字，從而不會(huì)影響MAX鎖定最大正數(shù)。這里我們可以明顯的看到B6單元格，當(dāng)目標(biāo)數(shù)字為負(fù)值時(shí)，公式出錯(cuò)。這就是MAX法相較于LOOKUP的明顯劣勢(shì)，因此情景三，還是推薦使用LOOKUP法提取數(shù)字。
以上，就是混合文本提取函數(shù)公式的三種進(jìn)階情景應(yīng)用。其中的公式均只能在特定條件下生效，缺點(diǎn)明顯，缺乏普適性，但簡(jiǎn)單高效，也較易理解，在觀察到對(duì)應(yīng)特征時(shí)，小花推薦使用對(duì)應(yīng)公式。
在下篇文章中，小花將為大家介紹并詳細(xì)拆解提取數(shù)值萬能公式和提取數(shù)字字符串萬能公式，有興趣深入學(xué)習(xí)的小伙伴請(qǐng)務(wù)必吃透今日公式。學(xué)習(xí)更多技巧，請(qǐng)收藏關(guān)注部落窩教育excel圖文教程。
＊＊＊＊部落窩教育-excel混合文本提取數(shù)字＊＊＊＊
原創(chuàng)：小花/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）
更多教程：部落窩教育
聯(lián)系客服
