当前位置:主页 > office知识 >

Excel字符替换神器:Substitute函数的用法

时间:2019-12-20 13:57


曾经有一个Excel函数的读音让兰色拗口,它就是Substitute ,今天给大家介绍一下这个函数的用法。

 

SubStitute函数

 

用途:对指定的字符串进行替换。

语法:

substitute(text,old_text,new_text,[instance_num])

 

翻释成中文更好理解

substitute(字符串,要被替换掉的字符,替换后的字符,[替换第几个])

 

在语法说明中,如果添加中括号,说明这人参数可以省略,所以Substitutue的第3个参数是可以省略的。

 

学语法太枯燥,赶紧上例子。

 

【例1】把字符串 excelpx中的px替换为 替换为 培训

公式为:

=SUBSTITUTE(D4,"px","精英培训")

 

 

在实际工作中,经常遇到字符串中含有大量空格的情况,这时可以用substitute函数全替换掉。

 

【例2】替换掉空值再查找。

 

空格可以影响Vlookup函数的查找,这时只需要Substitute函数来辅助,替换掉空格。

=VLOOKUP(SUBSTITUTE(G2," ",""),A:E,5,0)

 

 

太简单不过瘾?来个巧妙的用法

 

【例3】计算字符串中逗号的数量。

 

=LEN(C2)-LEN(SUBSTITUTE(C2,",",""))

 

用原来的字符长度 - 替换掉逗号的字符长度,刚好是逗号的数量

 

还是太简单?再来一个稍难些的

 

【例4】计算含中文的数字之和

 

=SUMPRODUCT(--SUBSTITUTE(B2:B5,"人",""))

 

注:substutite替换掉文本后的数字不能直接计划,需要用两个减号转换成数值型。因为涉及数组直接求和,所以用Sumproduct函数而不是Sum。

 

还有没有更复杂的,当然有。以前兰色分享过一个根据分隔符截取的公式:

 

【例5】把A列的科目拆到后面各列中

 

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

 

 

注:把横线替换成100个空值(足够大的数量),然后截取后再用trim函数把空格去掉。

 

兰色说:Excel中的每一个函数都有特定的用法,不一定常用,但需要它上场时还非它不可。