替换函数Substitute，用法大全

查找替换，是Excel中的常见操作，除了用命令完成之外，一些特殊情况下，还可以用函数完成，此函数就是Substitute，可以将指定数据中指定字符串的值替换为新值，此函数也因此而得名替换函数。

---

一、功能及语法结构。

功能：将字符串中的部分字符串以新字符串替换。

语法结构：=Substitute(源字符串,被替换字符串,替换字符串,[替换位置])；当省略“替换位置”时，默认从第一个位置开始替换。

基础示例：

目的：将“性别”中的“男”替换为1，“女”替换为2。

方法：

在目标单元格中输入公式：=IF(D3="男",SUBSTITUTE(D3,"男",1),2)。

解读：Excel 2019零基础小白逆袭Excel大神全套视频教程 | 知识兔

学习到这里，部分亲可能会有疑问：要完成上述需求，用【查找替换】岂不是更简单？为什么要用Substitute？其实，这要从函数的优势说起，函数可以构建和数据源之间的动态关联，当数据源发生了变化，函数可以自动更新获取最新结果。换句话说，函数具有自动化处理数据的能力，而基础操作却无法完成自动化处理的功能。

---

 

 

二、经典案例解读。

（一）隐藏手机号中间4位。

方法：

在目标单元格中输入公式：=SUBSTITUTE(C3,MID(C3,4,4),"****")。

解读：Excel 2019零基础小白逆袭Excel大神全套视频教程 | 知识兔

使用上述方法可以隐藏手机号中间的4位，达到加密的目的，那隐藏身份证号码中的出生年月是不是同样的道理呢？但要注意的是此操作是不可逆的。

---

（二）对含有单位的值进行求和。

方法：

在目标单元格中输入公式：=SUMPRODUCT(SUBSTITUTE(H3:H12,"元","")*1)。

解读：Excel 2019零基础小白逆袭Excel大神全套视频教程 | 知识兔

1、公式中，首先利用Substitute函数将H3:H12区域中的单位“元”替换为空值，然后×1，强制换换为数值，最后用Sumproduct函数对齐求和。

2、或在目标单元格中输入公式：=SUM(SUBSTITUTE(H3:H12,"元","")*1)，并用Ctrl+Shift+Enter填充即可。

Excel 2019零基础小白逆袭Excel大神全套视频教程 | 知识兔

---

 

（三）计算文本的数量。

方法：

在目标单元格中输入公式：=LEN(C3)-LEN(SUBSTITUTE(C3,"、",""))+1。

解读：Excel 2019零基础小白逆袭Excel大神全套视频教程 | 知识兔

公式中首先用Len函数计算源字符串的长度，然后用Substitute函数将分割字符“、”替换为空值，并计算长度，最后+1修正得到想要的结果，如果不理解+1的原因，可以查阅一下植树原理。