首页 excel笔记函数技巧正文

substitute函数的使用方法和实例解析

九天 函数技巧 2020-01-08 419 0 substitute

substitute,经典的替换函数,它的主要作是对指定的字符串进行替换,共有4个参数;

通俗语法:substitute(需要替换的文本,旧文本,新文本,[第n个旧文本]);

其中第4参数可以省略,表示替换所有的旧文本,跟其他函数组合,运用更灵活;

案例1、substitute函数批量对指定数字或文本进行替换

substitute函数素材图1.png

公式:SUBSTITUTE(A2,"中山市","广州市"),

说明:此处仅使用了三个参数,第四个参数省略,表示将所有符合条件的旧文本进行替换;

案例2:substitute函数配合其他函数灵活运用

1)substitute函数+len函数可以统计人数

substitute函数素材图2.png

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

说明:len是统计单元格字符的函数,空格也会计算在内;本例中,len(B2)统计了文字和标点符号,SUBSTITUTE(B2,"、","")将标点符号替换掉了,再使用len函数统计计出单元格不含标点符号的字符个数,两者之差就标点符号的个数,因为最后一个名字不带标点符号,所以在公式最后面“+1”,得出正确结果;

2)substitute函数将文本分拆

在《拒绝加班,如何把excel单元格中海量的不规则的文字和数字拆分出来》、《快下班了,老板要求统计一堆不规范的表格,请教同事后3分钟搞定》这两篇文章,日记坊有详细的禅述,这里就不再赘述;

substitute函数分拆文本3)substitute函数对带文本的数据求和

substitute函数素材图3.gif

公式:=sumproduct(substitute(C2:C11,"元","")*1)&"元"

说明:原理同例1相同,将元替换成空值,因为为其他原来是文本格式,所以*1后变成数值格式,使用sumproduct函数进行求和,最后用连接符"&"加上"元",使结果带上单位;

打赏
  • 文章发表:九天
  • 本文地址:https://rijifang.com/index.php/post/115.html
  • 声       明:转载请注明出处和附带本文链接!文章部份资料来自于网络,版权归原作者,尊重原创,注重分享;如涉版权问题,请联系本站删除!