Excel中替换函数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。
简而言之:SUBSTITUTE(旧文本,新文本,第几个)
二、Substitute函数小游戏
曾经流行过一首歌《老虎,老鼠,傻傻分不清楚》,我们今天就一起来玩一个游戏:替换文字游戏。
名称 | 要求 | 公式 |
老虎,老鼠,傻傻分不清楚 | 将老虎换成老鼠 | =SUBSTITUTE(A2,”老虎”,”老鼠”) |
老鼠,老鼠,傻傻分不清楚 | 将第二个老鼠变成老虎 | =SUBSTITUTE(A3,”老鼠”,”老虎”,2) |
老鼠,老虎,傻傻分不清楚 | 将老鼠换成老虎 | =SUBSTITUTE(A4,”老鼠”,”老虎”) |
老虎,老虎,傻傻分不清楚 | 将老虎换成老鼠 | =SUBSTITUTE(A5,”老虎”,”老鼠”) |
公式:=SUBSTITUTE(A2,”老虎”,”老鼠”),意思是将老虎替换成老鼠,没有最后一个参数,表示全部老虎字符替换为老鼠字符。
公式:=SUBSTITUTE(A3,”老鼠”,”老虎”,2),意思是将第2个老鼠字符替换成老虎。
特别注意:公式中的双引号都要在英文条件下输入,否则会出错。只要理解了这个游戏,那么你就理解了Substitute函数30%。
三、隐藏手机号码中间四位数字
有时候我们为了保护隐私,要隐藏中奖的手机号码中间四位数字,我们就能用Substitute函数实现这个目的。
B2=SUBSTITUTE(A2,MID(A2,4,4),”****”)
公式解读:MID(A2,4,4)是提取手机号码的中间四位数字,最后我们用星号进行替换即可。
GIF动图:
四、Len函数和SUBSTITUTE函数配合求人数
B2=LEN(A2)-LEN(SUBSTITUTE(A2,”、”,””)) 1
SUBSTITUTE(A2,”、”,””)意思是先将A2中的字符中的顿号全部替换掉,然后算其长度。LEN(A2)是没有替换掉顿号的长度,两者相减,就是顿号的数量。经观察,总人数等于顿号的数量再加上1。
GIF动图:
五、计算带单位的总人数
B2=SUMPRODUCT(SUBSTITUTE(A2:A9,”人”,””)*1)
公式解读:SUBSTITUTE(A2:A9,”人”,””)意思是将A列中的人字符替换为空值,最后乘以1,将文本转化为数值。最后再用SUMPRODUCT函数求和。
GIF动图: