用7個Excel公式詳細(xì)講解:文本提取函數(shù)的使用技巧
?
作者:老菜鳥來源:部落窩教育發(fā)布時間:2022-12-06 17:13:48點(diǎn)擊:2488
編按:
哈嘍,大家好,今天通過一個數(shù)據(jù)提取的例子,來給大家分享一些文本提取函數(shù)的使用技巧。涉及到的函數(shù)有:MID、FIND、LEFT、REPLACE、MIDB、FINDB、RIGHTB、TRIM、SEARCHB
在之前的教材中曾經(jīng)分享過一期常用的文本函數(shù)合集:
今天再通過一個典型的數(shù)據(jù)提取案例,了解更多的文本函數(shù)使用技巧。
案例如下圖所示,要在一串字符中把房號信息提取出來。
針對這個例子,給大家介紹7個不同的公式,涉及到的函數(shù)有:MID、FIND、LEFT、REPLACE、MIDB、FINDB、RIGHTB、TRIM、SEARCHB。
溫馨提示:由于篇幅所限,僅對每個公式的思路做介紹,不會設(shè)計函數(shù)的具體講解。
公式1:=MID(A2,FIND("-",A2)-1,FIND("定",A2)-FIND("-",A2)+1)
這個數(shù)據(jù)源看似雜亂無章,如果找不到規(guī)律的話是無法準(zhǔn)確提取出房號的。
仔細(xì)觀察后可以發(fā)現(xiàn),房號后面都有“定金”兩個字,而且房號是包含了“-”這個符號的。第一個“-”前面是一位數(shù)字,找到這個規(guī)律后方法就有了。
用FIND("-",A2)-1定位出房號的起始位置,FIND("定",A2)定位出房號的結(jié)束位置,FIND("定",A2)-FIND("-",A2)+1的作用就是用結(jié)束位置-起始位置,確定出房號的長度。
再用MID(數(shù)據(jù)源,起始位置,截取長度)就可以得到需要的結(jié)果。
公式2:=MID(LEFT(A2,FIND("定",A2)-1),FIND("-",A2)-1,9)
這個公式首先用LEFT(A2,FIND("定",A2)-1)截取出“定”字前面的內(nèi)容。
接下來只需要從“-”這個符號前面一位截取即可。因?yàn)榉刻柕拈L度都在9個字符以內(nèi),所以公式的意思實(shí)際上就是MID(“定”前面的內(nèi)容,“-”前面一位開始,9個字)。
公式3:=MID(REPLACE(A2,FIND("定",A2),99,),FIND("-",A2)-1,9)
這個公式與公式2的區(qū)別在于先用REPLACE(A2,FIND("定",A2),99,)把“定”字后面的內(nèi)容全部替換為空。
然后再用MID完成房號提取,這一步和公式2完全一樣。
公式4:=REPLACE(REPLACE(A2,FIND("定",A2),99,),1,FIND("-",A2)-2,)
這個公式的第一步與公式3一樣,先用REPLACE(A2,FIND("定",A2),99,)把“定”后面的內(nèi)容替換掉,再用一次REPLACE函數(shù)從左面第一個字符開始到“-”前兩個字符全部替換掉,整個公式相當(dāng)于用了兩次查找替換。
在介紹下面幾個公式之前,先給大家普及一個概念:字符和字節(jié)的區(qū)別。
簡單來說,字符指類字形單位或符號,包括字母、數(shù)字、運(yùn)算符號、標(biāo)點(diǎn)符號和其他符號,以及一些功能性符號。而字節(jié)(Byte)是計算機(jī)信息技術(shù)用于計量存儲容量的一種計量單位。
如果用字符數(shù)來計算數(shù)據(jù)長度的話,各種字母、數(shù)字、符號等等都是一個字,而如果用字節(jié)來計算數(shù)據(jù)長度的話,這里就有區(qū)別了,漢字、中文符號等等是兩個字節(jié),而數(shù)字、半角符號等等只能算一個字節(jié)。
在我們今天的這個例子中,“-”就是1個字節(jié),而“定”就是2個字節(jié)。
之所以要說明這個概念,是因?yàn)橄旅娴膸讉€公式都利用了字節(jié)和字符計算長度時的這種差異。
MIDB、FINDB、RIGHTB、SEARCHB等這些以B結(jié)束的函數(shù)都是以字節(jié)來計算的函數(shù)。
公式5:=MIDB(A2,FINDB("-",A2)-1,8)
仔細(xì)觀察不難發(fā)現(xiàn),當(dāng)房號只有7位的時候,實(shí)際上最后是有一個空格的,只有滿8位的才正常。
這是因?yàn)橛?span>MIDB函數(shù)提取8個字節(jié)而最后第8個字符又是雙字節(jié)的時候,就只能提取前面7個再補(bǔ)一個空格,因?yàn)椴豢赡馨训?span>8個字提取一半。
如果要求比較高的話,可以再加一個TRIM清除這個多余的空格,公式修改為:
=TRIM(MIDB(A2,FINDB("-",A2)-1,8))
下面這兩個公式也是用了字節(jié)計算的方式,有興趣的同學(xué)可以自己驗(yàn)證一下,就不一一贅述了。
公式6:=TRIM(MIDB(A2,SEARCHB("?",A2),8))
公式7:=TRIM(RIGHTB(LEFT(A2,FIND("定",A2)-1),8))
文本函數(shù)中這些結(jié)尾是B的函數(shù),在實(shí)際應(yīng)用中比較難理解,想研究明白的話只能自己多琢磨。
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
沒有Textjoin函數(shù),如何解決提取數(shù)據(jù)的問題?
八大查找函數(shù)公式,輕松搞定數(shù)據(jù)中的多條件查找
版權(quán)申明:
本文作者老菜鳥;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實(shí)戰(zhàn):按指定次數(shù)遞增數(shù)據(jù)
- 2019年全網(wǎng)最全—excel提取身份證信息合集!(建議收藏)-下篇
- 明明沒有重復(fù),Excel卻判定數(shù)據(jù)重復(fù),這是怎么回事?
- 文本格式的求和,及求和中最容易出現(xiàn)的問題解疑
- 致命缺陷:不懂一維表!
- 函數(shù)組合思維,你有嗎?
- 學(xué)會這2個公式,整理考勤數(shù)據(jù)只要一分鐘
- 就算被說是拍馬屁也成,今天你應(yīng)該這樣發(fā)Excel報表……
- 如何計算Excel單元格中的算式,四種求和方法請收好!