本篇文章給大家帶來了關于excel的相關知識，其中主要介紹了substitute函數的相關問題，該函數是excel最常用的文本函數之一，在數據分析過程中，常用于字符串的整理和清洗，下面一起來看一下，希望對大家有幫助。
相關學習推薦：excel
SUBSTITUTE函數的基礎語法是：
SUBSTITUTE(要替換的文本,舊文本,新文本,[替換第幾個])
最后一個參數，[替換第幾個]， 是可以省略的。
舉幾個小栗子，和大家分享下SUBSTITUTE函數的常用技巧和套路。
1，將數據中的某個值替換為另一個值
比如將B列數據里的二班，替換為一班。
C2輸入公式，并向下復制填充：
=SUBSTITUTE(B2,"二班","一班")
這里沒啥好解釋的了，就是將B列中單元格中的“二班”全部替換為“一班”。
2，隱藏手機號的中間5位
C2輸入公式，并向下復制填充：
=SUBSTITUTE(B2,MID(B2,4,5),"*****")
先使用MID函數取得B列號碼中的中間五位，再用字符串“*****”替換掉這部分內容。
3，對含單位的數據求和
B7輸入公式：
=SUMPRODUCT(SUBSTITUTE(B2:B6,"人",)*1)
先用SUBSTITUTE替換掉B列單元格中的“人”，得到文本型數字，乘以1后轉換成可以計算的數值，再用SUMPRODUCT函數進行求和。
4，數據分列，將B列數據按頓號進行分列
C2單元格輸入公式橫向拖動，并向下復制填充。
=TRIM(MID(SUBSTITUTE($B2,"、",REPT("?",100)),column(A1)*100-99,100))
REPT(” “,100)
先使用REPT函數，將空格重復100次，得到100個空格。
SUBSTITUTE($B2,”、”,REPT(” “,100))
使用SUBSTITUTE函數將姓名中的的間隔符號頓號替換為100個空格。
MID(SUBSTITUTE($B2,”、”,REPT(” “,100)),COLUMN(A1)*100-99,100)
再使用MID函數，依次從帶有空格的新字符串中的第1、第101、第201位……截取長度為100的字符。
這樣得到的字符串是帶有多余空格的，因此再使用TRIM函數將多余空格刪除掉。
5，混合文本中，計算人數個數
B2公式：
=len(A2)-LEN(SUBSTITUTE(A2,"、",))+1
LEN(A2)取得A2字符串的長度。
LEN(SUBSTITUTE(A2,”、”,))+1，替換掉人名之間的間隔符，也就是頓號，再用LEN計算該值的長度，最后加1，是因為最后一個人名沒有頓號。
用A2數值原有的長度減去被替換掉人名之間間隔符的長度，也就是人名的個數。
6，混合文本中，計算數值最大值。
B2數組公式：
=MAX((SUBSTITUTE(A2,ROW($1:$98),)A2)*ROW(1:98))
SUBSTITUTE(A2,ROW($1:$98),)A2
依次將數值1到98從A2替換為空，然后把替換后的結果和被替換值（1-98）進行比較 ，如果不相等，則證明A2中存在該數值。
最后將上述部分公式的運算結果，也就是邏輯值TRUE和FALSE，乘以被替換的值(1-98)，用MAX函數從中取得最大值。
7，計算某個值在某個范圍的最大連續次數
B2數組公式：
=MAX((SUBSTITUTE(PHONETIC(A2:A9),REPT("A",ROW(1:9)),)PHONETIC(A2:A9))*ROW(1:9))
PHONETIC(A2:A9)
將A2:A9的文本值黏合成一個值，以便SUBSTITUTE函數進行操作。
REPT(“A”,ROW(1:9))
把“A”重復1到9次。
SUBSTITUTE(PHONETIC(A2:A9),REPT(“A”,ROW(1:9)),)PHONETIC(A2:A9)
思路回到示例6，SUBSTITUTE函數將REPT函數的運算結果，在PHONETIC函數的運算結果里替換掉，然后和PHONETIC函數的原值進行比較。如果后者存在替換值，則被替換掉，此時和原值不相等，返回FALSE，否則返回TRUE。
最后依然把上述公式返回的邏輯值TRUE和FALSE，分別乘以ROW(1:9)，用MAX函數從中取得最大值。
相關學習推薦：excel
