Excel 的 SUBSTITUTE 函数:替换单元格中匹配的文本

在 Excel 中,要查找并替换特定词语或符号,可用 SUBSTITUTE。省略第四个参数会替换所有匹配项,输入 2 则只替换第二个匹配项。函数区分大小写,结果与源单元格分别保留。

指定原文本、查找文本和替换文本

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

第一个参数是原文本或单元格引用,第二个参数是查找文本,第三个参数是替换文本。可选的第四个参数表示从左向右数的匹配序号。2 指第二次匹配,与它在字符串中的字符位置无关。

Microsoft 关于 SUBSTITUTE 语法和参数的说明

比较全部替换和只替换第二项

在 A2 输入 AB-CD-cd-CD,再将表中的公式分别输入空白单元格。大写的 CD 出现两次,中间的小写 cd 不匹配。

公式 结果 变更内容
=SUBSTITUTE(A2,"CD","ZZ") AB-ZZ-cd-ZZ 替换两个大写的 CD
=SUBSTITUTE(A2,"CD","ZZ",2) AB-CD-cd-ZZ 只替换第二个大写的 CD
=SUBSTITUTE(A2,"CD","ZZ",3) AB-CD-cd-CD 没有第三个匹配项,返回原文

删除连字符和空格,同时保留前导零

在 A6 中把 00-27 04 输入为文本。如果 Excel 自动转换内容,输入时可在开头加英文单引号。第一个公式删除所有连字符并返回 0027 04。第二个也删除普通空格,返回 002704。

=SUBSTITUTE(A6,"-","")
=SUBSTITUTE(SUBSTITUTE(A6,"-","")," ","")

嵌套使用时,内层 SUBSTITUTE 的结果传给外层,就可以依次删除不同字符。标识码的前导零有意义时,请将结果保留为文本。公式无法恢复在输入时已丢失的零。

Microsoft 关于将前导零保留为文本的说明

没有替换成功时检查什么

检查源数据的大小写、横线类型和空格类型。查找普通空格时,不间断空格会保留。如果要删除首尾的普通空格,并让单词之间只留一个空格,可以使用 TRIM。

如果字符内容会变,但要改的位置始终相同,请用 REPLACE。例如要改每个代码从第 4 个字符起的两个字符,可以直接指定起始位置和字符数。