excel中批量替換函式（excel的替換函式）

在excel中，常用的替換函式有replace和substitute函式，這兩個函式都可以替換單元格中的部分內容，功能和ctrl H的功能類似，但是使用函式的目的一方面不會破壞原資料，另一方面與其他函式結合可以實現更多功能，對於substitute的引數=substitute（單元格，被替換的字串，新字串，指定替換第幾個），第四個引數可以省略，表示全部替換。而replace函式的引數=replace（單元格，從第幾個字元開始替換，替換的字元個數，新字串），從引數可以看出來這兩個函式的替換角度是有區別的，前者是直接指定把**替換為**，後者是從第幾個字元開始替換，替換幾個字元，替換成什麼。這兩種思路在工作中根據具體情況選擇簡單的一種即可，下面就通過例項看一看他們的使用方法吧。

一，substitute函式與replace函式的基本用法對比。在下圖中，要把身份證號碼中的出生日期替換為四個星號，以替換C2單元格中的身份證號為例，在D2單元格中輸入函式=REPLACE(C2,7,8,"****")即可。表示在C2單元格中，從左數第7位開始，往右數8個字元，把這8個字元替換為****。運用substitute函式時，在E2單元格中輸入函式=SUBSTITUTE(C2,MID(C2,7,8),"****")，這個函式中的mid函式表示在C2單元格中，從第7位開始，提取8位字元，返回的結果就是19901203，然後substitute函式表示把C2單元格中19901203替換為****。

二，利用substitute函式替換字元。在下圖中，如果要替換“滾滾長江東逝水，浪花淘盡英雄。”中的兩個“滾滾”，則輸入函式=SUBSTITUTE(A2,"滾","")，此處省略了第四個引數，表示把A2單元格中所有的“滾”字替換為空值。如果只替換一個“滾”，則輸入函式=SUBSTITUTE(A2,"滾","",1)，這裡第四個引數沒有省略，1表示只替換其中一個“滾”。

三、利用replace函式省略內容。在下圖中，要把B列中姓名的第一個字保留，後面的字全部用*代替。以B2單元格為例，在C2單元格中輸入函式 =REPLACE(B2,2,999,"**")，這個函式表示在B2單元格中，從第2個字元開始，後面999個字元全部替換為**，此處第3個引數只要輸入足夠大的數就行。

四、substitute函式與sumproduct函式結合進行求和。在下圖中，C列中資料不是數值，而是數字加文字組合，利用sum等數值函式無法求和。此時運用substitute函式與sumproduct函式組合就可以進行求和。函式公式為=SUMPRODUCT(--SUBSTITUTE(C2:C10,"元",""))&"元"。這個公式中的SUBSTITUTE(C2:C10,"元","")表示把C2到C10單元格的“元”替換為空值，前面--表示減負號，把單元格文字格式變成數值格式，然後利用sumproduct函式進行求和，最後用連線符加上“元”字。如果把sumproduct函式改成sum函式的話，就成了陣列函式，按下ctrl shift enter才能算出正確的結果。

五、substitute函式與len函式結合計算單元格重複字元的個數。在下圖中，要如何計算A2單元格中的3重複出現了多少次呢？輸入函式公式=LEN(A2)-LEN(SUBSTITUTE(A2,3,""))即可算出3出現了4次。此處SUBSTITUTE(A2,3,"")，表示把A2單元格中的3全部替換為空值，然後前面加上len函式表示去掉3以後單元格字串的長度，而len（A2）表示A2單元格字串的長度，兩個len函式相減就是重複值的個數了。

這就是excel中replace函式與substitute函式的一些常見用法，趕快試一下吧。