史上最全的文本函數典型用法盤點(第二期)
?
作者:小可來源:部落窩教育發(fā)布時間:2021-07-08 11:21:09點擊:1425
編按:
昨天,我們學習了九大文本函數系列案例用法的第一期——<史上最全的文本函數典型用法盤點(第一期)>。今天,我們繼續(xù)學習文本函數!第二期,小E給大家?guī)淼氖?/span>REPLACE和SUBSTITUTE兩大函數!它們被隱藏起來的秘密即將被公布……
各位讀者好!
大多文本函數的語法雖簡單,卻暗藏著許許多多結構語法不介紹的潛規(guī)則。REPLACE函數第三參數的省略妙用、SUBSTITUTE函數一二三參數鮮為人知的【秘密】……是不是迫不及待了呢~~
快跟小編一起來學習吧!
目錄先呈上~
一、REPLACE
知識樂園:
REPLACE(被替換字段,開始位置,替換長度,替換字段)。
敲黑板:如果第三參數為0或省略參數,可以實現類似插入字符串的功能!
1.1替換字符
要求:如圖,B列是電話,為了保護個人信息,現需要將電話號碼中的第4-7位號碼隱藏。
方法:在C3單元格輸入公式:=REPLACE(B3,4,4,"****"),向下復制填充公式。
解讀:從電話號碼的第4個字符開始,用字符串“****”替換掉其中的4個字符。
1.2插入字符
要求:A列是姓名加數字,現需要將姓名與數字在一個單元格分行顯示,即完成
方法:在F3單元格輸入公式:=REPLACE(E3,LENB(E3)-LEN(E3)+1,,CHAR(10)),向下復制填充公式。
解讀:LENB(E3)-LEN(E3)+1部分,先用LENB(E3)-LEN(E3)算出雙字節(jié)字符的長度(即漢字的長度),再+1得到漢字后面的位置;REPLACE函數省略第三參數,可在漢字后面插入字符CHAR(10),CHAR(10)是強制換行的代碼。
小提醒:若正確輸入公示后木有得到正確結果,請將單元格格式設置為自動換行。
1.3刪去字符
要求:如圖,A列是工號,現在要求去掉“-”及其后面的內容。
方法:在B14單元格輸入公式:=REPLACE(A14,FIND("-",A14),99,""),向下填充復制公式。
解讀:FIND("-",A14)部分,是查找出"-"在文本中的位置,再用REPLACE把從"-"起和其以后的內容全部替換為""(即空)。
1.4分段顯示
要求:將F列的電話號碼,分段顯示到G列。例如,“18285756946”顯示成“182 8575 6946”。
方法:在G14單元格輸入公式:=REPLACE(REPLACE(F14,4,," "),9,," "),向下復制填充公式。
解讀:這是REPLACE函數的嵌套。先在電話號碼的第4個位置插入" "(空格),完成第一次分段;再在修改后的文本的第9個位置插入一個" "(空格),完成第二次分段。
二、SUBSTITUTE
知識樂園:
(1)該函數區(qū)分大小寫查找,當第一參數源字符串中沒有包含第二參數指定的字符串時,函數結果返回源文本。
例如:將B20中的“excel”替換為“123”,由于B20單元格字符串中沒有“excel”只有“Excel”,所以結果返回源文本。
(2)當第三參數為空文本或者是省略該參數的值而只保留參數之前的逗號時,相當于將需要替換的文本刪除。
例如:在C21單元格輸入公式:=SUBSTITUTE(B21,"教程",)。結果只返回字符串“Excel”。
(3)當第四參數省略時,源字符串中所有與第二參數相同的文本都將會被替換。如果第四參數指定次數時,只有指定次數的第二參數文本會被替換。
例如:
1)省略第四參數,將文本中所有“教程”換成“excel”。
2)第四參數指定為2,則B23單元格中第二次的“教程”替換為“excel”。
2.1統(tǒng)計每小組人數
要求:統(tǒng)計出每一小組的人數。
方法:在C3單元格輸入公式:=LEN(B3)-LEN(SUBSTITUTE(B3,"、",""))+1,向下復制填充公式。
解讀:利用SUBSTITUTE函數將文本中所有的“、”換為""(空),再用LEN函數分別得出源文本長度和刪除“、”后的文本長度,兩者相減,得出文本中“、”的個數,再+1就是每組人數啦~
2.2計算平均分
要求:F列是每個人的分數,但是有的分數后面有單位,有的木有,需要在F9單元格計算出所有人的平均分。
方法:=AVERAGE(--SUBSTITUTE(F3:F8,"分",)),按
解讀:用SUBSTITUTE函數將“分”換為””(空);接著用“--”減負進行運算,將所有文本數字轉換為數值型數字;最后用AVERAGE函數計算出F列的平均分。
2.3刪除補位的0值
要求:A列是以逗號(,)隔開的“00”格式的數字,現需要刪除多余補位的0。
方法:在B13單元格輸入公式:=MID(SUBSTITUTE(A13,",0",","),1+(LEFT(A13)="0"),99),向下填充復制公式。
解讀:SUBSTITUTE(A13,",0",",")部分,是將文本中所有的“,0”替換為“,”,即刪去了除第一位數就是0以外的所有0。1+(LEFT(A13)="0"部分,表示若第一位數不是0,1+FALSE=1,則MID函數從去掉0后的文本的第1位開始提取出99個字符串,即提取出替換后所有的字符串;若第一位數是0,1+TRUE=2,MID函數則從替換后的文本的第二位數開始提取出99個字符串,即提取出除第一個0后的所有字符串。
今日分享暫時到這里啦,有木有漲知識呢~~~讀者老爺們,下次見!??!
本文配套的練習課件請加入QQ群:902294808下載。
做Excel高手,快速提升工作效率,部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你!
掃下方二維碼關注公眾號,可隨時隨地學習Excel:
相關推薦:
Excel數字提取技巧:從無規(guī)律文本中提取手機號的5種方法
Excel實用案例:SUBSTITUTE嵌套函數對文本單元格的判斷和計算
版權申明:
本文作者小可;同時部落窩教育享有專有使用權。若需轉載請聯(lián)系部落窩教育。