SUBSTITUTE函数用法详解

发布时间:2021-04-14 Excel函数大全

一、substitute函数基本语法

SUBSTITUTE函数用于对指定字符串进行替换。

语法结构为：=SUBSTITUTE(需要替换的文本,旧文本,新文本,[替换第几个])

二、SUBSTITUTE函数基本用法

1、将“件”替换为“箱”

此处利用SUBSTITUTE函数即可完成。

输入公式为：=SUBSTITUTE(B2,"件","箱")

2、删除数字之间的空格

输入公式为：=SUBSTITUTE(B2," ","")，之后按回车即可。

三、SUBSTITUTE函数经典用法

1、隐藏手机号码

为了保护隐私，需对手机号码隐藏处理，此处可利用SUBSTITUTE函数将手机号中间的4位数字替换为*符号，输入公式如下：

=SUBSTITUTE(B2,MID(B2,4,4),"****")，之后向下填充。

2、替换指定的数字

使用SUBSTITUTE函数可对指定数字替换，如下表，需要将房号的8栋统一替换为9栋，输入公式，从向至下填充：=SUBSTITUTE(A2,"8","9",1)

公式解释：此处只需替换第一个数字8，因此公式中加上第3个参数1，代表替换第1个数字8，即可完成。

3、统计人数

如下表，需要统计每天的报考人数，输入公式之后向下填充：

=LEN(B2)-LEN(SUBSTITUTE(B2,"，",""))+1

公式解释：先使用SUBSTITUTE函数，将报考人员间的逗号替换为空，注意：要替换的逗号需为中文状态下的，再使用LEN函数计算删掉逗号之后的字符串长度。

接着再计算出包括逗号的字符串总长度，减去删除逗号的字符串长度再加上1，即是报考人数。

4、文本拆分

如下表，若想将每个报考的人员进行拆分，输入公式之后向右向下填充：

=TRIM(MID(SUBSTITUTE($B2,"，",REPT(" ",100)),COLUMN(A1)*100-99,100))

公式解释：

REPT(" ",100) ：先使用REPT函数，将空格重复100次，得到100个空格；
SUBSTITUTE($B2,"，",REPT(" ",100))：使用SUBSTITUTE函数，将姓名的逗号替换为100个空格；
MID(SUBSTITUTE($B2,"、",REPT(" ",100)),COLUMN(A1)*100-99,100) ：再使用MID函数，依次从带有空格的新字符串中的第1、第101、第201位……开始截取长度为100的字符；
如此得到的字符串即是带有多余空格的，再使用TRIM函数将多余的空格删除即可。

5、带单位数据求和

如下表，在表格中录入数据时带有单位，如何求和统计？输入公式为：

=SUMproduct(SUBSTITUTE(C2:C11,"元","")*1)&"元"

公式解释：SUBSTITUTE(D2:D10,"元","")是将C列的“元”全部替换为空值，乘以1，将文本转换为数值，再使用sumproduct函数求和。最后使用连接符&加上“元”，使结果带上单位即可。

最新文章