梅英疏淡，冰澌溶洩，東風暗換年華。

--《望海潮·洛陽懷古》宋.秦觀

在Excel中，說到換不知道大家首先想到的是什麼函數呢？

​如果對函數了解比較多的話，可能會想到REPLACE函數以及Substitute函數。

這兩個函數的區别在于，REPLACE函數側重于位置點，Substitute函數則側重于内容的替換。

這兩個函數的具體的用法區别我們後面再講，今天主要和大家分享的是SUBSTITUTE函數的用法

首先，我們先看下SUBSTITUTE函數的基本用途含義。

SUBSTITUTE函數的用途是對指定的字符進行替換。

函數定義：

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

text ：字符串，可以是文本内容也可以是對含有文本的單元格的引用。

old_text ：要被替換掉的字符

new_text : 替換後的字符内容

Instance_num ：可以省略，代表替換text中的第幾個old_text,

知道了它的基本語法，下面就來給大家看下實際的應用吧。

案例一、名稱替換

我們把A1單元格内容的"彩虹"替換為"學習"，我們可以輸入公式：

=SUBSTITUTE(A1,"彩虹","學習")

案例二、去除字符間空格

把A列名單中的中間的空格去除，我們可以輸入公式：

=SUBSTITUTE(A2," ","")

案例三、手機号中間4位以"*"号顯示

有時為了保護我們的個人手機信息，常常需要把手機号碼中間部分隐藏，這時我們就可以使用Substitute函數了

在C2輸入公式：

=SUBSTITUTE(B2,MID(B2,4,4),"****")

MID函數取手機号碼的中間4位，再使用Substitute函數替換掉

案例四、搭配Vlookup函數查找

我們在使用Vlookup函數進行查找的時候，可能會遇到明明看起來一樣，可是就是查找不到，這時就要考慮是否存在空格了，我們可以搭配substitute函數來使用了

=VLOOKUP(SUBSTITUTE(F2," ",""),B1:D15,3,0)

案例五、統計部門人數

現在每個部門有一些成員，每個成員間使用"、"隔開，我們想統計每個部門有多少人，我們可以使用Len函數搭配Substitute函數實現

=LEN(B2)-LEN(SUBSTITUTE(B2,"、","")) 1

Len(B2)代表計算B2的總長度

SUBSTITUTE(B2,"、",""))代表把人員清單裡面的"、"全部替換為空；

再使用Len函數統計替換後的字符長度，兩個一減即統計"、"的個數，最後再加1，即為人員數了

案例六、帶單位文本求和

以下每個員工的銷售金額都帶有後綴"元"，如果我們想計算所有員工的銷售總額，直接使用SUM函數是不可以的，這時我們可以使用SUbstitute函數替換掉字符，再進行計算

=SUMPRODUCT(SUBSTITUTE(B2:B10,"元","")*1)

注意：

1、因為SUBSTITUTE(B2:B10,"元","")是數組計算，所以我們不能使用SUM函數了，故采用的是SUMPRODUCT函數，如果使用SUM函數，必須按Ctrl Shift Enter結束

2、因為SUBSTITUTE(B2:B10,"元","")替換後的金額是文本型，所以還需要把其轉換為數值型，故對其進行*1計算

案例七、數據分列

我們有如下的數據，其中項目欄位是把星期數以及課程名稱寫一起的，中間使用"-"隔開，我們想對其進行分列，此時也可以使用Substitute函數

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

公式解釋：

SUBSTITUTE($A2,"-",REPT(" ",99))：代表将A2單元格的"-"替換為99個空格

COLUMN(A1)*100-99：當向右拖動的時候，即分别生成1,101,201的數

再使用MID函數提取從1,101,201...等開始取數，取100位。

為什麼是取100位呢？

因為A列的字符串至少包含一個字符，當再加上99個空格時，所有位數至少是100

最後多餘的空格使用TRIM函數去除即可

案例八、拆分同類項

我們需要把A、B的橫向的數據拆分為D、E列豎向的數據，我們同樣可以借用Substitute函數來實現

=IFERROR(TRIM(MID(SUBSTITUTE(LOOKUP(,0/(A$2:A$6=D2),B$2:B$6),"、",REPT(" ",99)),100*COUNTIF(D$2:D2,D2)-99,100)),"")

以上就是給大家分享的Substitute函數的8種用法實例，如果有什麼問題，歡迎大家一起讨論。

如果覺得文章對你有幫助的話，希望大家幫忙點贊加分享哦~，謝謝

本文由彩虹Excel原創，歡迎關注，帶你一起長知識！

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部