文本提取那些事兒:超好用的SEARCHB函數(shù)和Mid函數(shù)
?
作者:小可來源:部落窩教育發(fā)布時間:2021-07-20 16:03:05點擊:3738
編者按:
Hello,大家好,這里是小E。我們處理數(shù)據(jù)時總免不了要查找并且提取一些重要的數(shù)據(jù),如何在上千甚至上萬行的數(shù)據(jù)中快速準確的進行查找和提取?小可老師給我們介紹了兩個利器:SEARCHB函數(shù)和Mid函數(shù),對于文本的查找提取它們是專業(yè)的!理論+案例,小可老師在線手把手教學(xué)。
哈嘍,大家好吖~今天小可給大家?guī)淼母韶浭?span>SEARCHB(”?”,文本)小妙招與MID函數(shù)的實用案例!我們一起去學(xué)習(xí)吧~~~
本期目錄先奉上
1.1統(tǒng)計以“182”開頭“6”結(jié)尾的號碼個數(shù)
一、SEARCH和SEARCHB
知識樂園:
SEARCH和SEARCHB函數(shù)查找不區(qū)分大小寫,而且可以使用通配符查找。
通配符:"?"匹配任意單個字符;"*"匹配任意一串字符。
若要查找實際的問號或星號,請在該字符前鍵入波形符(~)。
1.1統(tǒng)計以“182”開頭“6”結(jié)束的號碼個數(shù)
要求:A列是號碼,要求在B列統(tǒng)計以“182”開頭、“6”結(jié)尾的號碼個數(shù)。
方法:在B4單元格輸入公式“=COUNT(SEARCH("182???????6",A3:A10))”,按“Ctrl+Shift+Enter”三鍵結(jié)束。
解讀:公式為嵌套函數(shù),內(nèi)層是SEARCH函數(shù),外層是COUNT函數(shù)。
①SEARCH函數(shù)在號碼中查找出以“182”開頭、“6“”結(jié)尾,文本長度為11的號碼,查找結(jié)果以內(nèi)存數(shù)組的形式保存在公式中,結(jié)果如下:
{1;#VALUE!;#VALUE!;#VALUE!;#VALUE!;1;#VALUE!;#VALUE!};
②外層用COUNT函數(shù)對數(shù)字數(shù)據(jù)進行統(tǒng)計(COUNT函數(shù)對于空單元格、邏輯值或者文本數(shù)據(jù)都不計數(shù))。
小提醒:若在B3單元格輸入公式:=COUNT(SEARCH("182*6",A3:A10)),按“Ctrl+Shift+Enter”
1.2提取數(shù)字
要求:提取出“信息列”右側(cè)的連續(xù)數(shù)字。
方法:在G3單元格輸入公式“=MIDB(F3,SEARCHB("?",F3),99)”,向下復(fù)制填充公式。
解讀:SEARCHB和MID函數(shù)的嵌套使用。
①SEARCHB("?",文本)可以查找到文本中首個單字節(jié)字符的位置;
②再利用MID函數(shù)從首個單字節(jié)字符的位置開始,提取出后面的99個字符(即所有字符)。
小提示:輸入公式“=RIGHT(F3,2*LEN(F3)-LENB(F3))”也可以得出一樣的結(jié)果。
1.3提取數(shù)字(文字與數(shù)字混合排列)
要求:如圖,A列是文字與連續(xù)數(shù)字混合排列的句子,現(xiàn)需要單獨提取出句子中的數(shù)字到B列。
方法:在B15單元格輸入公式“=MIDB(A15,SEARCHB("?",A15),2*LEN(A15)-LENB(A15))”,向下復(fù)制填充公式。
解讀:三個函數(shù)組合的嵌套。
①SEARCHB("?",A15)部分:查找出首個單字節(jié)字符的位置;
②2*LEN(A15)-LENB(A15)部分:得出單字節(jié)字符的長度(關(guān)于LEN函數(shù)更詳細的解釋可以康康第一期的2.1、2.2哦~);
③最后利用MIDB函數(shù),從首個單字節(jié)字符的位置起,提取出單字節(jié)字符長度的文本串字符。
二、MID
2.1提取身份證號碼中的出生年月日
要求:B列為身份證號碼信息,要求提取出每個人的出生年月日并以“1988-07-18”的格式保存在C列。
方法:在C3單元格輸入公式“=TEXT(MID(B3,7,8),"0-00-00")”,向下復(fù)制填充公式。
解讀:MID和TEXT函數(shù)的嵌套使用。
①使用MID函數(shù)從身份證的第7位數(shù)起提取8個字符串;
②再借助TEXT函數(shù)將格式轉(zhuǎn)換為"0-00-00"。
2.2分列顯示年、月、日
要求:將E列的年月日按年、月、日的順序分別提取放置到F、G、H列。
方法:同時選中F3:H3單元格區(qū)域,輸入數(shù)組公式“=MID(E3,{1,5,7},{4,2,2})”,按“Ctrl+Shift+Enter”
解讀:MID函數(shù)的第二參數(shù)和第三參數(shù)都運用了常量數(shù)組形式,為提取年月日,應(yīng)該根據(jù)數(shù)組組成特點,分別從E列數(shù)組中的第1、5、7位分別提取4、2、2個字符串,結(jié)果存放在F3:H3單元格中。
悄悄說一句:文本函數(shù)的參數(shù)用對了數(shù)組將會非常方便~~~
2.3將一串數(shù)字拆分成單個數(shù)字并求和
要求:將下列圖表中的“數(shù)字串”的每個數(shù)拆分后求和,例如將“89652”拆分后求和等于“8+9+6+5+2=30”。
方法:在B16單元格輸入公式“=SUM(--(0&MID(A16,ROW($1:$9),1)))”, 按“Ctrl+Shift+Enter”
解讀:以“89652”為例作分析。
①MID(A16,ROW($1:$9),1)部分,利用MID函數(shù)把文本的前九個字符串分別提取出來,以內(nèi)存數(shù)組的形式保存在公式中,結(jié)果如下:{"8";"9";"6";"5";"2";"";"";"";""};
②0&MID(A16,ROW($1:$9),1)部分,雖然空文本""在有時候等價于數(shù)值0,但是并不等于數(shù)值0,為了避免下一步減負運算因為空文本而出現(xiàn)“#VALVE!”,所以前面用0連接上提取出的9個字符,結(jié)果如下:
{"08";"09";"06";"05";"02";"0";"0";"0";"0"};
③--(0&MID(A16,ROW($1:$9),1))部分,將文本型數(shù)字轉(zhuǎn)換為數(shù)值型數(shù)字,結(jié)果如下:
{8;9;6;5;2;0;0;0;0};
④最后用SUM函數(shù)對內(nèi)存數(shù)組數(shù)值進行求和。
2.4用MID函數(shù)判斷等級
要求:根據(jù)圖中的評級規(guī)則對A列同學(xué)的成績進行評級,評級結(jié)果依次展示在C列。
方法:在C25單元格輸入公式“=MID($A$41,B25/10+1,1)”,向下復(fù)制填充公式。
解讀:MID函數(shù)和數(shù)學(xué)計算結(jié)合。
①輔助列中,上行的分數(shù)與下行的評級一一對應(yīng)。例如0對差、10對差、20對差、30對差……90對優(yōu)、100對優(yōu);
②第二參數(shù),因MID函數(shù)會自動對小數(shù)參數(shù)作去尾處理,所以“/10”后不必再對商取整(即不必使用INT等取整函數(shù));
③B25/10+1作為在“差差差差差差中中良優(yōu)優(yōu)”(有11個漢字,不是10個)字符串中開始提取的位置。以C25單元格的88分為例,第二參數(shù)88/10去掉小數(shù)點后的數(shù)值結(jié)果為8,則在“差差差差差差中中良優(yōu)優(yōu)”的第8+1=9個字符起提取1個字符串,即“良”。
今日分享就到這啦,下次見!
本文配套的練習(xí)課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關(guān)注公眾號,可隨時隨地學(xué)習(xí)Excel:
相關(guān)推薦:
Excel實用案例:SUBSTITUTE嵌套函數(shù)對文本單元格的判斷和計算
Excel數(shù)字提取技巧:從包含文字的單元格中提取所有數(shù)字的萬能公式
Excel數(shù)字提取技巧:從無規(guī)律文本中提取手機號的5種方法
Excel數(shù)字提取技巧:用簡單公式從混合文本中提取數(shù)字的3種情景
版權(quán)申明:
本文作者小可;同時部落窩教育享有專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。
最熱教程
- 像綠皮火車一樣長像珠穆拉瑪峰一樣高的Excel表怎么操作才方便?
- Power Query實戰(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單元格中的算式,四種求和方法請收好!