Excel字符替換神器:Substitute函數的用法

曾經有一個Excel函數的讀音讓蘭色拗口，它就是Substitute ，今天給大家介紹一下這個函數的用法。

SubStitute函數

用途：對指定的字符串進行替換。

語法：

substitute(text,old_text,new_text,[instance_num])

翻釋成中文更好理解

substitute(字符串，要被替換掉的字符,替換後的字符,[替換第幾個])

在語法說明中，如果添加中括號，說明這人參數可以省略，所以Substitutue的第3個參數是可以省略的。

學語法太枯燥，趕緊上例子。

【例1】把字符串 excelpx中的px替換為 替換為 培訓

公式為：

=SUBSTITUTE(D4,"px","精英培訓")

在實際工作中，經常遇到字符串中含有大量空格的情況，這時可以用substitute函數全替換掉。

【例2】替換掉空值再查找。

空格可以影響Vlookup函數的查找，這時只需要Substitute函數來輔助，替換掉空格。

=VLOOKUP(SUBSTITUTE(G2," ",""),A:E,5,0)

太簡單不過癮？來個巧妙的用法

【例3】計算字符串中逗號的數量。

=LEN(C2)-LEN(SUBSTITUTE(C2,"，",""))

用原來的字符長度 - 替換掉逗號的字符長度，剛好是逗號的數量

還是太簡單？再來一個稍難些的

【例4】計算含中文的數字之和

=SUMPRODUCT(--SUBSTITUTE(B2:B5,"人",""))

註：substutite替換掉文本後的數字不能直接計劃，需要用兩個減號轉換成數值型。因為涉及數組直接求和，所以用Sumproduct函數而不是Sum。

還有沒有更複雜的，當然有。以前蘭色分享過一個根據分隔符截取的公式：

【例5】把A列的科目拆到後面各列中

=TRIM(MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100)

註：把橫線替換成100個空值(足夠大的數量），然後截取後再用trim函數把空格去掉。

蘭色說：Excel中的每一個函數都有特定的用法，不一定常用，但需要它上場時還非它不可。

如果你是新同學，長按下面二維碼 - 識別圖中二維碼 - 關注，就可以每天和蘭色一起學Excel了。