「如何從Excel單元格中的文本值提取指定的數(shù)據(jù)出來？」
就比如做電商的同學(xué)可能經(jīng)常遇到收貨地址需要拆分成省市區(qū)的問題。
但是用戶提交的地址信息不一定規(guī)范，就導(dǎo)致拆分的時候需要人工一個一個識別。
本期內(nèi)容，以溪帶你看看工作中常見的文本拆分與提取的案例問題。
如果有你現(xiàn)在正在遇到的問題，可以直接套用。
關(guān)注以溪同學(xué)，收藏加星，get更多Excel知識技能！
「案例列表」
mid\left\right文本提取函數(shù)使用
地址拆分省市區(qū)
提取指定字符-分隔的指定個數(shù)字符
只提取數(shù)字或者字母
1. mid\left\right文本提取函數(shù)使用
Excel中有專門的文本提取函數(shù)，其中mid、left、right分別用于從中間、左側(cè)、右側(cè)提取文本中的指定長度內(nèi)容。
依次舉例說明：
函數(shù)參數(shù)：
MID(text,start_num,num_chars)
第一個參數(shù)是待提取的文本字符串，第二個是開始提取的字符位置數(shù)，第三個參數(shù)是從文本中提取的字符數(shù)
=MID(A2,3,2)
函數(shù)參數(shù)：
LEFT(text, [num_chars])
第一個參數(shù)是待提取的文本字符串，第二個參數(shù)不填默認(rèn)為1，代表提取的字符數(shù)。
=LEFT(A2,2)
函數(shù)參數(shù)：
RIGHT(text, [num_chars])
第一個參數(shù)是待提取的文本字符串，第二個參數(shù)不填默認(rèn)為1，代表提取的字符數(shù)。
=RIGHT(A2,2)
可以注意到，這三個參數(shù)，都有幾個共同點。
- 要提供待提取的文本字符串。
- 要告訴函數(shù)從哪提取。
- 提取多少個字符。
理解了上面這個問題，那解決實際案例的所有「關(guān)鍵點」就在2、3兩點上了。
2. 地址拆分
如果地址數(shù)據(jù)的省市區(qū)之間有分隔符的，可以直接使用「數(shù)據(jù)-分列-按指定字符分列」即可完成拆分。
如果是像下圖這種沒有分隔符的地址，有兩種方法可以實現(xiàn)拆分。
通過上面的3個函數(shù)，我們知道，想要拆分提取字符，必須要知道從哪提取，提取多少。
所以，第一步，我們需要知道在地址中，省這個字符的具體位置，以及省字符前面有多少字，就能直接提取出省這個字符串了。
在這里引入兩個函數(shù)，一個是find，一個是len。
find函數(shù)
函數(shù)作用就是用于查找指定字符在字符串中的字符數(shù)位置，函數(shù)最終返回值是一個數(shù)字。 FIND(find_text,within_text,start_num)
find(要找哪個字符,在哪個字符串里找,從第幾個字符位置開始找)
len函數(shù)參數(shù)
len函數(shù)用于返回指定字符串一個有多少個字符數(shù)
LEN(text)
len(文本字符串)
除了查找和統(tǒng)計字符數(shù)，Excel也提供findb與lenb函數(shù)，函數(shù)核心功能與find和len都一樣。
唯一的區(qū)別就是，帶b的函數(shù)，代表查找或計數(shù)的是字節(jié)數(shù)，反之是字符數(shù)。
其中日語、中文（簡體）、中文（繁體）以及朝鮮語一個字符算2個字節(jié)數(shù)。參考下圖理解，find和findb用于查找同學(xué)位于以溪123同學(xué)中的位置。
回到拆分地址上，那第一個，就是查找省這個字位于地址中的位置。
=FIND('省',A2)
如此得到省的位置后，我們便可以通過left函數(shù)，直接提取出省名稱。
=LEFT(A2,FIND('省',A2))
前面的是不是都很成功，但是最后一個自治區(qū)，就識別不出來了。
主要是我國的行政區(qū)劃分，不是所有的省級行政區(qū)都是省結(jié)尾的，還包括有市、區(qū)。
所以這里，我們需要通過數(shù)組公式，來一次性提取出省、市、區(qū)這三個字在地址中的位置。
數(shù)組公式如下：
=FIND({'省','市','區(qū)'},A2)
「注意數(shù)組公式，需要按照數(shù)組公式輸入方法使用」
數(shù)組公式使用方法
- 需提前選中承接數(shù)組公式結(jié)果的單元格區(qū)域
- 再輸入數(shù)組公式
- 最后需要按數(shù)組確認(rèn)鍵 CTRL+SHIFT+回車 確認(rèn)公式
目前只是把地址中省市區(qū)出現(xiàn)的位置找到了，還需要對數(shù)字進行對比，最小的那個，代表最先出現(xiàn)，也就是省級行政單位的位置。
所以我們使用min函數(shù)對其嵌套，得到最小位置數(shù)，再使用left函數(shù)提取，就能正確獲得一級行政區(qū)名稱。
但是由于min函數(shù)不能統(tǒng)計包含#VALUE!的錯誤值數(shù)據(jù)，所以我們在find公式中的地址參數(shù)那里，手動拼接一個省市區(qū)字符串，使其不管怎么樣，都不會返回錯誤值。確保min函數(shù)正確運行。
數(shù)組公式如下：
=LEFT(A2,MIN(FIND({'省','市','區(qū)'},A2&'省市區(qū)')))
至此第一個省級名稱，提取完畢。
如果將原地址中的省級名稱去除，那么剩下的地址中，就只包含地級和縣級行政區(qū)地址信息。
使用substitute函數(shù)，就可以替換字符串中的指定字符為空，也就是替換為''。
函數(shù)公式如下：
=SUBSTITUTE(A2,B2,'')
我們借用了剛剛提取的省級名稱，生成了新的地址，基于這個地址我們采用同樣的方法提取地級行政區(qū)名稱。
只需要把省級行政區(qū)的公式中的地址A2全部替換成上面的subtitute函數(shù)公式，再把對應(yīng)的地級行政區(qū)的后綴，市、區(qū)、州、盟，全部修改，就可以了。
最終的數(shù)組公式如下：
=LEFT(SUBSTITUTE(A2,B2,'')&'市區(qū)州盟',MIN(FIND({'市','區(qū)','州','盟'},SUBSTITUTE(A2,B2,'')&'市區(qū)州盟')))
最后一個直接使用substitute函數(shù)替換即可。
數(shù)組公式如下：
=SUBSTITUTE(A2,B2&C2,'')
如果你不想用上面這么長的公式，又或者地址信息沒有這么完整，存在不清晰的問題??！
「那就使用方方格子插件來完成地址提取，速度飛快?！?/strong>
操作路徑：方方格子-高級文本處理更多-提取地址-選擇地址范圍-確定-點擊結(jié)果存放單元格-確認(rèn)
最終結(jié)果如下圖，幾秒鐘就搞定了。
即使地址不是特別規(guī)范，也能大概匹配拆分出來。
當(dāng)然，最終還需要仔細(xì)檢查一下是否存在拆分出錯的情況。
3. 提取指定字符-分隔的指定個數(shù)字符
如下圖數(shù)據(jù)所示：
文本中用-分隔各個信息，其中第二個是產(chǎn)品名稱，最后一個是核銷狀態(tài)。
如果是比較規(guī)范的數(shù)據(jù)，就可以先手動提取一行，如下圖：
然后按快捷鍵CTRL+E，智能拆分提取，就能提取出對應(yīng)的數(shù)據(jù)。
但是，這個案例，CTRL+E沒法很好的拆分出來，比如產(chǎn)品名稱，此時可以選擇使用函數(shù)公式，將產(chǎn)品名稱和核銷狀態(tài)提取出來。
提取產(chǎn)品名稱：
=MID(A2,FIND('-',A2)+1,FIND('-',A2, FIND('-',A2)+1)-FIND('-',A2)-1 )
提取最后一個核銷狀態(tài)：
=TRIM(RIGHT(SUBSTITUTE(A2,'-', REPT(' ',LEN(A2)) ),LEN(A2)))
「提取產(chǎn)品名稱公式原理解析：」
- 使用find函數(shù)找到第一個指定字符-的位置數(shù)，此時find函數(shù)默認(rèn)從字符串第一個字開始找。
- 然后接著繼續(xù)用find函數(shù)找指定字符-，但是此時，我們find函數(shù)的第三個參數(shù)就寫第一次find函數(shù)的結(jié)果值+1，也就是從第一個指定字符-的下一個字符位置開始查找，由此找到了第二個-的位置。
- 至此mid函數(shù)的2、3參數(shù)已經(jīng)完全找到了。
「提取最后一個核銷狀態(tài)公式原理解析」
- 使用substitute函數(shù)，將所有的指定字符-替換為長度和字符串長度一致的空格。替換后圖片如下圖。
ps. 需要注意第一步中，我們使用rept函數(shù)，用來生成字符長度和原始字符串長度一致的空格
- 使用right函數(shù)，從替換后的字符串右側(cè)，提取長度為原本字符串長度的文本。此時的文本就是空格和最后一個核銷狀態(tài)數(shù)據(jù)
- 使用trim函數(shù)，去除文本中的空格
4. 只提取數(shù)字或者字母
如下圖所示，字母和數(shù)字在文本中的不同位置，現(xiàn)在想要將字母和數(shù)字全部提取。
從文本中提取字母和數(shù)字的公式如下：
=SUBSTITUTE( MIDB( A2, SEARCHB('?',A2), 2*LEN(A2)-LENB(A2)), '-','')
「從字符串中提取字母數(shù)字公式原理解析：」
- 公式使用了字節(jié)和字符兩種不同的計算方法。使用minb函數(shù)提取指定字節(jié)數(shù)的數(shù)據(jù)，lenb和len分別統(tǒng)計字節(jié)和字符數(shù)量。
- 由于字母數(shù)字和符號都是1個字節(jié)，中文是2個字符，通過searchb查找任意1個字節(jié)在字符串中的出現(xiàn)位置，就能定位字符串中第一個字母數(shù)字或者符號出現(xiàn)位置，進而通過midb函數(shù)提取。
- 提取位數(shù)則通過len和lenb的計數(shù)來計算出來，lenb減去len得到的就是中文字符的數(shù)量也就是2，換算就是4個字節(jié)，最后用lenb減去中文字符的字節(jié)數(shù)，就得到字母數(shù)字和符號的字節(jié)數(shù)。
- 最終使用substitute函數(shù)去除指定的符號，就提取完成了。
如果只想提取字母或者數(shù)字，則使用下面的公式：
只提取大小寫字母的數(shù)組公式，請使用數(shù)組三鍵CTRL+SHIFT+回車確認(rèn)公式：
=CONCAT( IF( ( (CODE(MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1))>=65)*(CODE(MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1))<=90) )+( (CODE(MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1))>=97)*(CODE(MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1))<=122)), MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1) ,'' ) )
如果只提取數(shù)字，則數(shù)組公式如下：
=CONCAT( IF( (CODE(MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1))>=48)*(CODE(MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1))<=57), MID(A2,ROW(INDIRECT('1:'&LEN(A2))),1) ,'' ) )
「從字符串中單獨提取字母或數(shù)字公式原理解析：」
- 上面的提取字母和數(shù)字，都使用了相同的原理，那就是code函數(shù)，對不同字符的對應(yīng)編碼，其中數(shù)字0到9，編碼為48到57，大寫字母A到Z，編碼為65到90，小寫字母a到z，編碼為97到122。
- 公式是數(shù)組公式，使用了mid將字符串拆分成單獨的字符，再通過code函數(shù)得到編碼，與對應(yīng)編碼對比，區(qū)分字母和數(shù)字，最終通過if函數(shù)判斷保留字母或數(shù)字，使用concat函數(shù)將保留的字符拼接在一起。
- 其中用到了sequence序列函數(shù)用法以及filter函數(shù)的多條件式判斷，建議在以溪主頁，查看對應(yīng)文章，深入了解。
如果用數(shù)組公式覺得太麻煩，也可以使用Excel插件來提取字符，如果你會編寫正則表達式，那無論是多奇怪的字符，都可以通過插件運行正則表達式提取指定的字符出來。
插件提取方法路徑如下：
方方格子-高級文本處理-更多
同時在提取更多里面，還支持提取鏈接以及之前說的拆分地址。
在高級提取功能里，支持額外4種提取需求，如圖所示。
以上，就是以溪總結(jié)的文本字符串提取的相關(guān)案例，如果你有實際需求沒有在以上案例，可以留言討論。
聯(lián)系客服
