Excel教程:Substitute函数,1分钟解决这些文本替换难题,效率翻倍!

点击【开通SVIP会员免费学】

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

SUBSTITUTE（需要替换字符的文本，旧字符串，新字符串，[替换第几个])

1、替换所有空格

在输入人员姓名的时候，喜欢在两个字的姓名中间输入空格，与三个字的姓名两端对齐，看起来是美观了，但对后期数据处理会带来麻烦，该怎么替换调所有的空格呢？

输入公式：=SUBSTITUTE(A2," ","")

用SUBSTITUTE函数将空格替换为空。

2、给手机号码加密

为保护个人信息，防止手机号码外泄，现需将手机号码第4-7位以星号（*）显示，该怎么操作呢？

输入公式：

=SUBSTITUTE(B2,MID(B2,4,4),"****")

先用MID函数从手机号码第4位开始提取，提取4位；

再用SUBSTITUTE函数将从手机号码提取的4位替换为"****"。

3、换行显示

各部门中的姓名是以顿号（、）隔开显示，现要将各姓名换行显示，该怎么做呢？

输入公式：

=SUBSTITUTE(B2,"、",CHAR(10))

CHAR(10)：换行符

用SUBSTITUTE函数将"、"替换为换行符。

4、统计部门人数

统计各部门的人数，你还在一个个的数吗？

输入公式：

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

SUBSTITUTE(B2,"、","")将"、"替换为空，返回"花花小玉儿"；

LEN(SUBSTITUTE(B2,"、",""))部分返回替换后的字符个数5；

LEN(B2)部分返回B2单元格字符个数6；

用B2单元格字符个数6-替换后的字符个数5，再加1，返回6-5+1=2。

5、带有相同文本单位的求和

B列金额都带有文本单位“元”，直接用SUM函数求和会返回0。

该怎么处理呢？

输入公式：

=SUMPRODUCT(SUBSTITUTE(B2:B9,"元",)^1)&"元"

先用SUBSTITUTE函数将“元”替换掉；

替换后的金额是文本型{"2560";"6740";"12500";"7486";"10560";"15000";"3890";"5600"}，通过^1转换为数值{2560;6740;12500;7486;10560;15000;3890;5600}；

再用SUMPRODUCT函数求和，返回64336；

最后用连接符&连接“元”，返回64336元。

6、带“~”的查找

业务员“雨夜~”中含有通配符“~”，直接用VLOOKUP函数查找会返回错误值#N/A，遇到这种情况该怎么办呢？

输入公式：

=VLOOKUP(SUBSTITUTE(D2,"~","~~"),A:B,2,0)

“~”作为通配符，在查找通配符本身时，需要在其前输入“~”，该公式中用SUBSTITUTE函数将“~”替换成“~~”。

7、提取会计科目

该案例可以使用分列的方法，但如果使用公式，该怎么写呢？

输入公式：

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

SUBSTITUTE($A2,"-",REPT(" ",99))部分：将A列会计科目中的“-”替换成99个空格；

COLUMN(A1)*100-99部分：公式向右填充时自动构成序列1，101，201，将它作为MID函数的第二参数，从第几个字符开始提取；

每个字符串至少1个字符，再加上99个空格，所以第三参数可为100；

用MID函数提取后，字符中含有空格，用TRIM函数删除字符串中多余的空格。

今天的分享就到这，如果教程对大家有用，希望大家多多分享点赞支持小编哦！你的每一次点赞和转发都是支持小编坚持原创的动力。
点击阅读原文一键登录官网，海量视频vip任意学！(可试看)