轉自excel大講堂,侵聯繫刪
前面的文章裡給大家介紹了幾個特別強大的函數,如VLOOKUP,LOOKUP,SUMIFS,SUMPRODUCT等函數,今天給大家再介紹一個比較強大的函數——
SUBSTITUTE函數。函數名片
●名稱:SUBSTITUTE
●功能:將某一串文本中的值替換成其他值。
●套路:SUBSTITUTE(替換目標,目標裡的那個字符,在換的新字符,[替換第幾個])
1、替換單個字符
將下表中的文本中的“金融”兩個字替換成“財務”。
在B2單元格中輸入公式:
=SUBSTITUTE(A2,"金融","財務"),按Enter鍵完成。如下圖所示:
2、替換指定位置的字符
將下表中的文本中的第2個“夢想”兩個字替換成“夢想起點”。
在B2單元格中輸入公式:
=SUBSTITUTE(A2,"金融","財務"),按Enter鍵完成。如下圖所示:
注:該函數的最後一個參數可以指定具體替換的位置,是可選參數。
3、統計人數
下表中是某一次參會的人員情況,現統計人員參會情況。
在C2單元格中輸入公式:
=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1,按Enter鍵完成。如下圖所示:
注:LEN函數是提取字符串的長度,上題中是先計算原字符串的長度,然後再將字符串的逗號替換成空白,計算替換後字符串的長度,最後兩個減加1就是姓名的個數。
4、隱藏手機號碼中的重要部分
下表中某次獲獎的人員名單,要求對外公佈時隱藏中間的5位。
在C2單元格中輸入公式:
=SUBSTITUTE(B2,MID(B2,4,5),"*****"),按Enter鍵完成。如下圖所示:
注:MID函數是截取手機號碼中的中間5位,然後將其替換成5個星號即可。
5、文本求和
下表中是某次活動的一個購買清單,每個金額後面跟了一個元,要求求和。
在D5單元格中輸入公式:
=SUMPRODUCT(--SUBSTITUTE(D2:D4,"元","")),按Enter鍵完成。如下圖所示:
注:“--”是減負的意思,強制地把文本型的數字轉換成數值型的。然後使用SUMPRODUCT來求和。
以上就是關於SUBSTITUTE函數的強大妙用,歡迎留言評論^_^
閱讀更多 趣說video 的文章