戳藍(lán)字“Excel星球”關(guān)注我哦。菜單→資源禮包?領(lǐng)取海量學(xué)習(xí)教程??HI，大家好，我是星光。眾所周知，文本數(shù)據(jù)處理是表格中最常見(jiàn)的問(wèn)題之一，諸如按位置截取數(shù)據(jù)、按關(guān)鍵字拆分、合并、替換數(shù)據(jù)、將數(shù)據(jù)格式化等等。
今天就給大家分享一下，Excel有哪些常用的文本類函數(shù)照例能堅(jiān)持看到最后的都是勇士。
常用的按索引位置截取數(shù)據(jù)的函數(shù)由MID/LEFT/RIGHT/LEN/MIDB等。
如上圖所示的數(shù)據(jù)，B列是虛擬的身份證號(hào)，需要從中提取出生年份。身份證的第7~10位是年份，MID函數(shù)可以從指定位置（第7個(gè)）截取指定長(zhǎng)度（4個(gè)）的數(shù)據(jù)。由于文本函數(shù)返回的結(jié)果通常是文本值，所以公式最后乘1，將其轉(zhuǎn)換為數(shù)值。
LEFT是左邊的意思，該函數(shù)的意思就是最左邊的幾個(gè)人站出來(lái)…RIGHT是右邊的意思，它的意思是最右邊的幾個(gè)人站出來(lái)…2丨按關(guān)鍵字截取數(shù)據(jù)FIND和SEARCH函數(shù)可以搜索關(guān)鍵字在文本值中出現(xiàn)的位置。兩者不同的是，F(xiàn)IND區(qū)分字母大小寫，不支持使用通配符，SEARCH函數(shù)與之相反。如上圖所示的數(shù)據(jù)，需要從B列的信息中提取人名。人名的長(zhǎng)度不定，但都處于關(guān)鍵字'-'之后。=MID(B2,FIND('-',B2)+1,99)或=MID(B2,SEARCH('-',B2)+1,99)
MID函數(shù)的第3個(gè)參數(shù)表示截取字符的長(zhǎng)度，這里設(shè)置為99，直接飽和式攻擊。如果實(shí)際截取字符的長(zhǎng)度小于該值，例如人名'看見(jiàn)星光'只有4個(gè)字符，則按實(shí)際字符長(zhǎng)度截取。
3丨按關(guān)鍵字替換數(shù)據(jù)SUBSTITUTE和REPLACE函數(shù)可以執(zhí)行查找替換的工作。前者是按關(guān)鍵字替換，后者是按位置替換。如上圖所示的數(shù)據(jù)，需要將B列信息中的'班'替換為'號(hào)'。
=SUBSTITUTE(B2,'班','號(hào)')
但這個(gè)公式會(huì)將B2單元格中所有的'班'都替換為'號(hào)'，例如'13班-張小班'會(huì)被替換為'13號(hào)-張小號(hào)'。
=SUBSTITUTE(B2,'班','號(hào)',1)
SUBSTITUTE函數(shù)的第4個(gè)參數(shù)可以指定替換第幾個(gè)關(guān)鍵字。
如上圖所示的數(shù)據(jù)，C列是虛擬的手機(jī)號(hào)碼，需要對(duì)第4~7位的數(shù)字加密。
REPLACE函數(shù)可以從數(shù)據(jù)源字符串的指定開(kāi)始位置（第2參數(shù)），將指定長(zhǎng)度的字符串（第3參數(shù)），替換為目標(biāo)值（第4參數(shù)）。CONCAT和TEXTJOIN函數(shù)可以將多個(gè)字符串合并成一個(gè)，后者還可以指定合并時(shí)的間隔符。如上圖所示的數(shù)據(jù)，需要在D列將同行的A~C列數(shù)據(jù)合并。如下圖所示的數(shù)據(jù)，還是在D列將同行的A~C列數(shù)據(jù)合并，但需要以小橫杠作為間隔符。TEXTJOIN函數(shù)第1參數(shù)指定合并后的間隔符，第2參數(shù)表示是否忽略空值，1為忽略，0為不忽略。5丨字符串拆分
很久以前有個(gè)姓羅的大佬說(shuō)過(guò)，天下大勢(shì)合久必分分久必合。既有字符串合并就有字符串拆分這主要使用到TEXTSPLIT/FILTERXML/SUBSTITUTE等函數(shù)或套路。如上圖所示的數(shù)據(jù)，需要從B列信息中提取姓名。
=INDEX(TEXTSPLIT(B2,'-'),2)
TEXTSPLIT函數(shù)將B2單元格的內(nèi)容按分隔符'-'拆分，INDEX函數(shù)從中提取第2個(gè)數(shù)據(jù)——不過(guò)，TEXTSPLIT目前僅存在于365的beta版本。=TRIM(MID(SUBSTITUTE(B2,'-',REPT(' ',50)),49,50))
=FILTERXML('<a><b>'&SUBSTITUTE(B2,'-','</b><b>')&'</b></a>','a/b[2]')
TEXT函數(shù)可以對(duì)數(shù)據(jù)按指定格式進(jìn)行轉(zhuǎn)換，比如將數(shù)值轉(zhuǎn)換為日期等。如上圖所示的數(shù)據(jù)，需要將A~B列的數(shù)據(jù)合并，C列為模擬結(jié)果。=A2&TEXT(B2,'-yyyy年m月d日')
TEXT函數(shù)將B2單元格的日期按照'-yyyy年m月d日'的格式轉(zhuǎn)換為文本日期形式，再使用運(yùn)算符&合并成一個(gè)文本值。至于為什么不直接使用公式=A2&B2，是因?yàn)椤愕葧?huì)，我再找個(gè)往期推文的鏈接▼=A2&B2如此簡(jiǎn)單的公式為什么會(huì)出錯(cuò)
Excel工作表函數(shù)看起來(lái)很豐富，但實(shí)際上……，更糟糕的是它更新補(bǔ)缺的效率更……。一個(gè)TEXTSPLIT函數(shù)被催了20年才發(fā)布實(shí)驗(yàn)版，支持正則更是遙遙無(wú)期，甚至到現(xiàn)在都還沒(méi)有按數(shù)據(jù)類型提取字符的函數(shù)o(?Д?)っ如果你需要按數(shù)據(jù)類型提取數(shù)據(jù)，需要了解以下潛規(guī)則▼。
通常我們把'吖'作為最小的漢字，把'咗'作為最大的漢字，如果一個(gè)字符既比吖大又比咗小，它就被認(rèn)為是漢字。如果一個(gè)字符串存在于0123456789中，很明顯，它就是數(shù)字。1/17運(yùn)算結(jié)果為0.0588235294117647，它包含了0~9所有的數(shù)字。大寫字母A和Z的code編碼分別為65/90，小寫字母a和z的code編碼分別是97/122，當(dāng)字符的code編碼處于這兩個(gè)區(qū)間內(nèi)即為字母。根據(jù)以上潛規(guī)則，得出以下數(shù)組公式如上圖所示的數(shù)據(jù)，需要從A列分別提取中文名、英文名和聯(lián)系方式。
=LET(_n,MID(A2,SEQUENCE(LEN(A2)),1),TEXTJOIN('',1,IF(_n>'吖',_n,'')))
=LET(_n,MID(A2,SEQUENCE(LEN(A2)),1),_b,UPPER(_n),TEXTJOIN('',1,IF((_b>'A')*(_b<'Z'),_n,'')))
=LET(_n,MID(A2,SEQUENCE(LEN(A2)),1),TEXTJOIN('',1,IF(ISNUMBER(FIND(_n,1/17)),_n,'')))
還有一些常用或不常用的文本處理函數(shù)。比如，LEN函數(shù)計(jì)算字符串的長(zhǎng)度，UPPER/LOWER將字母轉(zhuǎn)大/小寫，TRIM函數(shù)消除字符串頭尾的空格并將中間連續(xù)的多個(gè)空格只保留一個(gè)，CLEAN清除字符串中不可見(jiàn)干擾字符等，以上案例中多少都有涉及到了，就不再舉例子展示了……揮揮手說(shuō)再見(jiàn)，睡個(gè)回籠覺(jué)去，想起啥以后再補(bǔ)吧合什，額米豆腐，記得右下角點(diǎn)個(gè)贊哈。需要系統(tǒng)學(xué)習(xí)Excel，卻找不到優(yōu)質(zhì)教程？學(xué)習(xí)Excel的過(guò)程中遇到疑難問(wèn)題，卻找不到人及時(shí)作出解答？加入我的付費(fèi)社群，與4500+在線會(huì)員一起，同微軟全球最有價(jià)值專家（MVP）全面精進(jìn)表格之道。??????
加入我的Excel會(huì)員，全面學(xué)習(xí)Excel透視表 函數(shù) 圖表 VBA PQ想學(xué)啥學(xué)啥??本文由公眾號(hào)“Excel星球”首發(fā)。
