SUBSTITUTE函数：将字符串中的部分字符串以新字条串替换

excel函数中，substitute函数我们取：把……替换成……的意思来进行理解。

substitute函数的分类属于：文本函数
打开excel表格，点击编辑栏前的插入函数图标fx，弹出插入函数窗口，在选择类别中选择分类：文本函数，可看到该函数的官方解释：



文本函数substitute的含义和参数：
将字符串中的部分字符串以新字符串替换
substitute(text,old_text,net_text,instance_unm)

• 函数公式：=SUBSTITUTE(要搜索的文本, 搜索字符串, 替换内容,替换次数)

• 参数释义：

• 要搜索的文本：需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用
• 搜索字符串: 需要替换的文本
• 替换内容: 用于替换的新文本
• 替换次数（选填）: 为一数值，用来指定以新文本替换第几次出现的旧文本，如果指定了替换次数，则只有满足要求的旧文本被替换；否则将用新文本替换文本中所有出现的所有的旧文本。

实例一：
下图中，使用了substitute函数，想去掉调休时间中的字符H，只显示成数字形式。

公式为：
＝substitute(g2,right(g2,1),)
解释：
就是把G2单元格中，要替换的部分是G2最右侧的1个字符，替换成的内容为空

而结算的结果中，H列存在一个并没有把H替换为空的情况。
原因是此对应的G列中的原数据中，存在空格，导致把最后1个空格进行了替换。

我们来进行一下演示，以及正常的操作：



=SUBSTITUTE(G2,"H",)
意思是，把G2单元格内的H字符，替换成空，所以，对修改后的公式，就实现了移动H符号的效果。
以上公式，等同于
=SUBSTITUTE(G2,"H","")
解释：当第二个参数以逗号结束时，第3个参数就默认为空了，这里，第3个参数可以不写，或者写的时候，使用1对引号就可以了。

升级：
如果要把修改后运算的结果中的空格进行去除，应该怎么办：
再修正公式：
=--SUBSTITUTE(G2,"H",)
增加2个负号即可。
解释：负负得正，就是把原本为文本的字符，通过负负的运算，转化成了数值，自然中间的空格就没了。

上面的gif动画演示，是直接全部替换excel单元格中所有的字符，如果在一个单元格中，有多个相同的字符，且实际中只想替换第几个字符时，添加第4个参数就可以了。


如上图，只想替换第二个点的时候。就是一种实际的情况

下面继续上gif动画演示教程，看一下excel函数中substitute的用法。



演示教程中演示了替换所有点和只替换第2个点的操作方法，在于2个单元格使用了不同的公式，如下：
D3单元格公式
=SUBSTITUTE(B3,".",)
E3单元格公式
=SUBSTITUTE(B3,".","",2)

区别看到了吗？E3单元格的公式中的第4个参数，指定的就是替换第几个，那如果要替换第1个点，也只需要把E3单元格中的2，修改为1就可以了。

除了这种方法之外，还可以通过其他的方式进行实现，比如分列，再组合等

原始数据带单位，使用substitute可以批量进行单位的替换，同时可以配合sum函数实现直接求和运算。



f8单元格公式：
=SUBSTITUTE(D8:D13,"元","")
看上面的截图可以得知，使用替换后运算的结果值为文本，默认的对齐方式为左对齐。

f9单元格公式：
=SUM(--SUBSTITUTE(D8:D13,"元",""))
substitute公式前面的2个负号的目的，就是把文本的结果转化为数值，再进行最后的求和运算。

实例：获取单元格内某个字符所出现的次数

假设你要计算字符“1”在 A2中出现的次数。 在单元格 b2 中键入以下公式：

=LEN(A2)-LEN(SUBSTITUTE(A2,"1",""))

单元格 b2 的值为 3，因为字符 “1” 在 A2 中出现三次。

分析​：

len(a2)是获取​A2单元格的总长度。

SUBSTITUTE(A2,"1","")是把a2单元格的所有的1进行删除，也就是替换为空的意思，再配合len

LEN(SUBSTITUTE(A2,"1",""))此时的意思就是删除​1后的长度多少。

让总长度减掉，已经删除所有1后的长度，结果就是1​所出现的总次数。

拓展：

如果要求出A2：A7区域中所有的1出现的总次数，应该如何解答？
1，可以先求出每个单元格内1出现的次数，再使用sum。
2，直接使用sum数组进行求和：=sum(LEN(A2:a7)-LEN(SUBSTITUTE(A2:a7,"1",""))),使用三键结束。