Excel中的替换神技，替换函数Substitute用法大全，值得学习收藏

在使用WPS时对Excel数据进行查找替换是常见操作，除了我们常用的查找替换命令，我们有时对于一些特殊情况，我们就需要使用到Substitute函数，主要是用于将指定数据中指定字符串的值替换为新值。今天就跟大家一下Substitute函数的常见用法和使用技巧，希望对大家有所帮助。

第一：Substitute函数介绍

Substitute函数其实就是表格文本替换函数，主要是用于将指定数据中指定字符串的值替换为新值。

语法

Substitute(Text,old_text,new_text,instance_num)

￭Text 为需要替换其中字符的文本，或对含有文本的单元格的引用。

￭Old_text 为需要替换的旧文本。

￭New_text 用于替换 old_text 的文本。

￭Instance_num 为一数值，用来指定以 new_text 替换第几次出现的 old_text。

如果指定了 instance_num，则只有满足要求的 old_text 被替换；

否则将用 new_text 替换 TEXT 中出现的所有 old_text。

第二：经典用法解读

（一）文本替换

如下图所示将A1单元格中的“赵飞”替换成“赵云”，只需在目标单元格中输入公式：=SUBSTITUTE(A1,”赵飞”,”赵云”)

（二）去掉字符的空格

如下图所示将A1单元格中的姓名中的空格去掉，只需在目标单元格中输入公式：=SUBSTITUTE(A1,” “,””)

（三）将“性别”中的“男”替换为2，“女”替换为1。

如下图所示，我们将表格中的性别这一列中的“男”替换为2，“女”替换为1，只需在目标单元格中输入公式：=IF(B2=”女”,SUBSTITUTE(B2,”女”,1),2)

（四）隐藏手机号中间4位以”*”号显示

有时为了保护个人手机信息不被泄露，常常需要把手机号码中间部分隐藏，这时我们就可以使用Substitute函数隐藏手机号中间的4位，达到加密的目的，具体操作如下

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

解读：

上面的公式使用到了SUBSTITUTE()函数和MID()，MID()函数返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定。

MID()函数语法：

MID(text,start_num,num_chars)

Text 是包含要提取字符的文本字符串。

Start_num 是文本中要提取的第一个字符的位置。文本中第一个字符的 start_num 为 1，以此类推。

先通过MID(D2,4,4)获取手机号从左侧第四位开始，然后去四位数，最后通过SUBSTITUTE(）函数把取得的数值都替换”****”。

那么同样的道理，也可以通过此方法隐藏身份证号码中的出生年月或者其它制定信息，但要注意的是此操作是不可逆的。

（五）对带单位的数值进行求和

我们在平时使用WPS办公时，经常会遇到需要对带有单位的数值进行求和，这时如果直接使用求和公式的话就无法处理，我们就可以通过SUBSTITUTE()函数在进行求和。

在求和单元格中输入公式=SUMPRODUCT(SUBSTITUTE(D2:D8,”件”,””)*1)，按Enter回车键即可获求和。

解读：

SUMPRODUCT函数公式介绍

SUMPRODUCT（array1,array2,array3, …）

Array1,array2,array3, … 为 2 到 30 个数组，其相应元素需要进行相乘并求和。

说明

￭数组参数必须具有相同的维数，否则，函数 SUMPRODUCT 将返回错误值 #VALUE!。

￭函数 SUMPRODUCT 将非数值型的数组元素作为 0 处理。

同样先使用SUBSTITUTE(D2:D8,”件”,””)就是把数值中的单位替换成空，然后*1后使用SUMPRODUCT函数进行数组相乘求和。

（六）统计文本数量

统计文本数量的情况我们经常用在统计人数这块，如下图所示每个部门参会人员使用”、”隔开，我们想统计每个部门有多少人，我们可以使用Len函数搭配Substitute函数实现。

在目标单元格中输入公式：=LEN(B2)-LEN(SUBSTITUTE(B2,”、”,””)) 1

解读：

1、公式中首先用Len函数计算源字符串的长度，Len(B2)代表计算B2的总长度。

2、LEN(SUBSTITUTE(B2,”、”,””))代表把人员清单里面的”、”全部替换为空，并计算长度。

3、最后 1修正得到想要的结果，如果不理解 1的原因，可以查阅一下植树原理。

Excel中的替换神技，替换函数Substitute用法大全，值得学习收藏

相关推荐

发表评论