Excel中SUBSTITUTE函數(shù)用法總結(jié)（函數(shù)substitute的用法）

手機如何做表格：點擊查看

SUBSTITUTE函數(shù)的基礎(chǔ)語法是：

SUBSTITUTE(要替換的文本,舊文本,新文本,[替換第幾個])

最后一個參數(shù)，[替換第幾個]， 是可以省略的。

舉幾個小栗子，和大家分享下SUBSTITUTE函數(shù)的常用技巧和套路。

比如將B列數(shù)據(jù)里的二班，替換為一班。

C2輸入公式，并向下復(fù)制填充：

這里沒啥好解釋的了，就是將B列中單元格中的“二班”全部替換為“一班”。

C2輸入公式，并向下復(fù)制填充：

先使用MID函數(shù)取得B列號碼中的中間五位，再用字符串“*****”替換掉這部分內(nèi)容。

B7輸入公式：

先用SUBSTITUTE替換掉B列單元格中的“人”，得到文本型數(shù)字，乘以1后轉(zhuǎn)換成可以計算的數(shù)值，再用SUMPRODUCT函數(shù)進行求和。

C2單元格輸入公式橫向拖動，并向下復(fù)制填充。

REPT(” “,100)

先使用REPT函數(shù)，將空格重復(fù)100次，得到100個空格。

SUBSTITUTE($B2,”、”,REPT(” “,100))

使用SUBSTITUTE函數(shù)將姓名中的的間隔符號頓號替換為100個空格。

MID(SUBSTITUTE($B2,”、”,REPT(” “,100)),COLUMN(A1)*100-99,100)

再使用MID函數(shù)，依次從帶有空格的新字符串中的第1、第101、第201位……截取長度為100的字符。

這樣得到的字符串是帶有多余空格的，因此再使用TRIM函數(shù)將多余空格刪除掉。

B2公式：

LEN(A2)取得A2字符串的長度。

LEN(SUBSTITUTE(A2,”、”,))+1，替換掉人名之間的間隔符，也就是頓號，再用LEN計算該值的長度，最后加1，是因為最后一個人名沒有頓號。

用A2數(shù)值原有的長度減去被替換掉人名之間間隔符的長度，也就是人名的個數(shù)。

B2數(shù)組公式：

SUBSTITUTE(A2,ROW($1:$98),)<>A2

依次將數(shù)值1到98從A2替換為空，然后把替換后的結(jié)果和被替換值（1-98）進行比較 ，如果不相等，則證明A2中存在該數(shù)值。

最后將上述部分公式的運算結(jié)果，也就是邏輯值TRUE和FALSE，乘以被替換的值(1-98)，用MAX函數(shù)從中取得最大值。

B2數(shù)組公式：

PHONETIC(A2:A9)

將A2:A9的文本值黏合成一個值，以便SUBSTITUTE函數(shù)進行操作。

REPT(“A”,ROW(1:9))

把“A”重復(fù)1到9次。

思路回到示例6，SUBSTITUTE函數(shù)將REPT函數(shù)的運算結(jié)果，在PHONETIC函數(shù)的運算結(jié)果里替換掉，然后和PHONETIC函數(shù)的原值進行比較。如果后者存在替換值，則被替換掉，此時和原值不相等，返回FALSE，否則返回TRUE。

最后依然把上述公式返回的邏輯值TRUE和FALSE，分別乘以ROW(1:9)，用MAX函數(shù)從中取得最大值。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。