SUBSTITUTE與REPLACE函數(shù)
SUBSTITUTE函數(shù)為替換字符，REPLACE函數(shù)為替換位置。這兩個(gè)函數(shù)的語法內(nèi)容如下。
替換字符：
SUBSTITUTE(text,old_text,new_text,[instance_num])
替換位置：
REPLACE(old_text,start_num,num_chars,new_text)
稍微觀察下可知，函數(shù)的語法已簡明扼要地寫出來了每個(gè)參數(shù)的含義。
1．基礎(chǔ)使用
下面看一下替換字符SUBSTITUTE函數(shù)的基礎(chǔ)用法。
D9單元格的公式為“=SUBSTITUTE(C9,'-','@')”，從基礎(chǔ)語法中看到，第2個(gè)參數(shù)old_text表示舊文本，第3個(gè)參數(shù)new_text表示新文本。也就是說，把字符串中的舊文本全部換成新文本，于是就有了D9單元格中的效果，把所有的“-”全部換成了“@”，如圖6-1所示。
圖6-1SUBSTITUTE函數(shù)
有時(shí)只想單純地把其中某一種字符刪除，而不是替換成其他字符，怎么辦？把對(duì)應(yīng)的字符換成文本空''（連著兩個(gè)英文半角的雙引號(hào)）就可以了，于是有D10單元格的公式“=SUBSTITUTE(C10,'-','')”。
這個(gè)函數(shù)還有第4個(gè)參數(shù)instance_num，下面通過一個(gè)實(shí)例來看看它的意義。C13單元格的內(nèi)容為“ABACADAEAF”，D13單元格的公式為“=SUBSTITUTE(C13,'A','-',3)”，如圖6-2所示。第4個(gè)參數(shù)為數(shù)字3，它的結(jié)果為“ABAC-DAEAF”。這個(gè)公式是把字母“A”替換為字符“-”，但是此時(shí)并不是所有的字母A都替換了，只有第3個(gè)A被替換了。第4個(gè)參數(shù)instance_num的作用就是控制替換第n個(gè)。
進(jìn)一步觀察，C14單元格的內(nèi)容為“ABaCADAEAF”，使用相同的公式“=SUBSTITUTE(C14,'A','-',3)”進(jìn)行操作，結(jié)果為“ABaCAD-EAF”。我們發(fā)現(xiàn)同樣是替換第3個(gè)A，替換的只有大寫字母，小寫字母a卻未被替換。所以，SUBSTITUTE函數(shù)是區(qū)分大小寫的，替換內(nèi)容必須與公式完全一致才能替換，效果如圖6-2所示。
圖6-2SUBSTITUTE第4參數(shù)
接下來介紹替換位置的REPLACE函數(shù)，REPLACE可以翻譯為“取代”，所以它所取代的就是某個(gè)位置，下面是REPLACE函數(shù)的基礎(chǔ)用法。
F9單元格的公式為“=REPLACE(C9,5,1,'ABC')”，對(duì)比基礎(chǔ)的語法，第二個(gè)參數(shù)為start_num，即開始的位置；第三個(gè)參數(shù)為num_chars，指字符的長度；第四個(gè)參數(shù)為new_text，即被替換成新的文本。那么這個(gè)公式整體上怎樣理解呢？將C9單元格的字符串，從第5位字符開始取1位字符長度，也就是把原字符串中的第二個(gè)短橫線替換為“ABC”。
F10單元格的公式為“=REPLACE(C10,3,6,'%')”，是指把C10單元格的字符串，從第3位開始取6個(gè)字符，也就是“-South”這一串字符替換為“%”，完成效果如圖6-3所示。
圖6-3REPLACE函數(shù)基礎(chǔ)用法
從這里可以看出，在替換時(shí)REPLACE只認(rèn)識(shí)“位置”，把原來占該位置的內(nèi)容全部刪掉，然后把新字符串插入此處，替換前后的字符數(shù)并不要求長度完全一致。
2．案例：刪除字符串中的數(shù)字或字母
C17單元格為字符串“0A12Bc345Def6”，可以看出，D17單元格的結(jié)果為“ABcDef”，所有數(shù)字都沒了，只保留了字母，如圖6-4所示，這是怎么做到的？
圖6-4替換所有數(shù)字
先把D17單元格的公式貼出來：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C17,1,''),2,''),3,''),4,''),5,''),6,''),7,''),8,''),9,''),0,'')
看到了什么？公式十層嵌套，公式太長看不懂！、
從公式的結(jié)構(gòu)看，公式內(nèi)容幾乎都是一樣的。就是先把1替換為空，然后把2替換為空，3，4，…，9，0，可以說沒有任何技術(shù)難度，除了寫的時(shí)候會(huì)讓人有些煩躁。接下來繼續(xù)看一個(gè)更讓人煩躁的公式。
如圖6-5所示，D21單元格的結(jié)果為“0123456”，所有字母都沒有了，只剩下了數(shù)字。結(jié)合上面的思路我們要怎么寫公式呢？
圖6-5替換所有字母
逐一把英文字母替換，難道要寫26層嵌套？不對(duì)！SUBSTITUTE函數(shù)是區(qū)分大小寫的，算上小寫，那一共要寫52層嵌套……
思路完全正確，下面先把公式寫出來：
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(C21),'A',''),'B',''),'C',''),'D',''),'E',''),'F',''),'G',''),'H',''),'I',''),'J',''),'K',''),'L',''),'M',''),'N',''),'O',''),'P',''),'Q',''),'R',''),'S',''),'T',''),'U',''),'V',''),'W',''),'X',''),'Y',''),'Z','')
公式雖然很長，但是僅用27層就夠了。仔細(xì)看看其中的一個(gè)關(guān)鍵點(diǎn)——UPPER(C21)，它表示什么意思？
我們通過Excel的幫助信息可以看到，UPPER是將文本轉(zhuǎn)換為大寫字母。于是，UPPER(C21)就把字符串從“0A12Bc345Def6”變?yōu)榱恕?A12BC345DEF6”，所有的小寫字母都“長大了”，然后只替換大寫的A~Z即可。
提示：額外普及兩個(gè)函數(shù)。
LOWER：將文本轉(zhuǎn)換為小寫字母，與UPPER完全相反。LOWER(C21)的結(jié)果為“0a12bc345def6”。
PROPER：文本字符串的首字母及文字中任何非字母字符之后的任何其他字母轉(zhuǎn)換成大寫，將其余字母轉(zhuǎn)換為小寫。這一段內(nèi)容是Excel幫助中的原文，讀起來有點(diǎn)繞，簡單地說，PROPER就是把每個(gè)單詞首字母大寫，其他字母都小寫。例如，“=PROPER('thisisaTITLE')”的結(jié)果為：ThisIsATitle。
如果工作中要求替換字母或數(shù)字，可以將以上兩個(gè)公式保存在計(jì)算機(jī)中，用的時(shí)候直接復(fù)制，更換其中所引用的單元格即可，不用從頭開始寫一遍，正所謂“前人栽樹，后人乘涼”。
有的讀者會(huì)嫌公式太長，技術(shù)含量低，不夠帥氣。但工作中應(yīng)先以解決問題為基準(zhǔn)，再考慮帥氣。在論壇中，有很多高手寫過這種提取字符的公式，幾乎全都是晦澀難懂，充分體現(xiàn)了解決這種問題的難度。所以，更好的處理辦法就是使用VBA。
后面會(huì)講解一個(gè)自定義函數(shù)，專門對(duì)付這種混亂的字符提取問題。
提示：Excel在2003版本中只能接受7層嵌套，所以想寫7層以上的嵌套公式還需要借助定義名稱，十分麻煩。在2007及以上版本可以接受64層嵌套，所以不是太過“奇葩”的編寫，都不會(huì)超出可接受的嵌套層數(shù)。
3．案例：電話號(hào)碼升位
在我國的座機(jī)歷史中，多地都經(jīng)歷過電話號(hào)碼升位。例如，將7位座機(jī)號(hào)的第2位后面增加一個(gè)數(shù)字“8”，升位為8位座機(jī)號(hào)，這要怎么做呢？
如圖6-6所示，D25單元格的公式為“=REPLACE(C25,3,0,8)”，其中，第三個(gè)參數(shù)是0，表示從第3個(gè)字符開始，取0個(gè)字符長度，將其替換為數(shù)字8。這0個(gè)字符長度表示什么都沒有，于是就成了在第3位插入一個(gè)數(shù)字“8”的效果。
圖6-6電話號(hào)碼升位
FIND的含義是找到，SEARCH的含義是尋找，這兩個(gè)函數(shù)的語法如下。
找到：
FIND(fifind_text,within_text,[start_num])
尋找：
SEARCH(fifind_text,within_text,[start_num])
這兩個(gè)英文單詞的意思差不多，而且函數(shù)的語法也基本一致。這里面翻譯成“找到”和“尋找”，其細(xì)節(jié)之處還是有區(qū)別的，這會(huì)在后面章節(jié)進(jìn)行講解。
1．常規(guī)用法
如圖6-7所示，這是FIND函數(shù)和SEARCH函數(shù)相同的基礎(chǔ)用法。
圖6-7基礎(chǔ)用法
以FIND函數(shù)為例，F(xiàn)IND(fifind_text,within_text,[start_num])，第1個(gè)參數(shù)fifind_text表示查找的文本。在哪里找呢？第2個(gè)參數(shù)within_text告訴我們在這里找。
D9單元格的公式為“=FIND('天津',C9)”，就是查找“天津”這兩個(gè)字在C9單元格中的位置，于是找到了在第1位的天津，所以得到結(jié)果1。
F9單元格的公式為“=FIND('河北',C9)”，在C9單元格中“河北”兩個(gè)字位于第4位。
下面看看SEARCH函數(shù)的公式。
D10單元格的公式為“=SEARCH('市',C10)”，在C10單元格中“市”字位于第3位。
F10單元格的公式為“=SEARCH('玄武',C10)”，結(jié)果是錯(cuò)誤值“#VALUE!”。這是因?yàn)镃10單元格的字符串是“北京市東城區(qū)”，這里并沒有“玄武”二字，所以返回錯(cuò)誤結(jié)果。
在這一部分常規(guī)查找中，F(xiàn)IND和SEARCH是完全相同的。
2．案例：以橫線分段提取字符
在實(shí)際工作中，會(huì)遇到各種各樣的特殊情況，那么怎樣才能擴(kuò)展一些思路呢？如圖6-8所示，模擬的是有些公司編碼的規(guī)則，每一部分代表一個(gè)層級(jí)，各層級(jí)之間使用橫線連接，然后得到唯一的編碼值?，F(xiàn)在要提取第一個(gè)“-”和第二個(gè)“-”之間的部分，得到G14:G15單元格區(qū)域的結(jié)果。要怎么做呢？
圖6-8提高用法
接下來是思路拆解，既然要獲取兩橫線之間的部分，那么先來找一找兩個(gè)橫線都在哪里？
對(duì)于A-SW-0001-A，首先在D14單元格編寫公式“=FIND('-',C14)”，得到結(jié)果2，說明第一個(gè)“-”在字符串的第2位。那怎樣查到第二個(gè)“-”的位置呢？
回歸到基礎(chǔ)語法可以發(fā)現(xiàn)，F(xiàn)IND有第三個(gè)參數(shù)“[start_num]”，意思是開始的數(shù)字，那它是不是表示從第幾位開始找呢？假設(shè)是這樣，我們在E14單元格輸入公式“=FIND('-',C14,D14+1)”，看到結(jié)果為5。說明第二個(gè)“-”是在原字符串的第5位。D14單元格的結(jié)果是第一個(gè)“-”的位置，然后公式中的“D14+1”，也就是告訴FIND函數(shù)不用從頭開始找了，在第一個(gè)“-”后面找就可以了，這樣就能成功地跳過了第一個(gè)“-”，找到第二個(gè)“-”。
我們把D14:E14單元格區(qū)域的公式向下復(fù)制到D15:E15單元格區(qū)域，如圖6-9所示，就確定了C15單元格字符串中“-”的位置。
圖6-9橫線位置
那么用什么提取字符呢？用MID函數(shù)。這個(gè)函數(shù)我們在5.1節(jié)學(xué)過。MID從第一個(gè)橫線的位置起始，然后長度為兩個(gè)橫線位置的差值，那么F14單元格的公式為“=MID(C14,D14,E14-D14)”，如圖6-10所示。
圖6-10提取字符1
F14單元格得到的結(jié)果是“-SW”，前面多了一個(gè)“-”。這是因?yàn)閺牡谝粋€(gè)“-”開始提取，所以就一起提取了。想去掉它很簡單，把MID的第2個(gè)參數(shù)+1，即將公式改成“=MID(C14,D14+1,E14-D14)”，如圖6-11所示。
圖6-11提取字符2
輸入公式后，結(jié)果為“SW-”，后面又多了一個(gè)“-”進(jìn)一步修正公式為“=MID(C14,D14+1,E14-D14-1)”，如圖6-12所示。
圖6-12提取字符3
這樣結(jié)果就完全正確了。我們是借用了兩個(gè)輔助單元格D14和E14才完成F14單元格的公式，那么可不可以不用輔助單元格D14和E14，直接用一個(gè)公式搞定呢？
從F14單元格的結(jié)果入手，先把里面的“E14”全部改為E14單元格的公式，于是F14的公式整合為：
=MID(C14,D14+1,FIND('-',C14,D14+1)-D14-1)
接下來進(jìn)一步整合D14單元格的公式：
=MID(C14,FIND('-',C14)+1,FIND('-',C14,FIND('-',C14)+1)-FIND('-',C14)-1)
如圖6-13所示，這里只剩下引用了原始數(shù)據(jù)的C14單元格。刪除輔助列，只保留最終的整合公式列，結(jié)果完全正確。
圖6-13提取字符整合公式
一開始就寫這種長長的公式，一定會(huì)有很多不理解地方。但結(jié)合前面講的分步操作，將公式整合到一起，就容易多了。很多公式都是這樣一步步打磨出來的。
聯(lián)系客服
