excel中的替代函数——replace和substitute函数的应用实例

在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函数的一些常见用法，赶快试一下吧。