Substitute函数使用简介
在许多时候可能需要对某个文本字符串中的部分内容进行替换。我们除了可以使用Excel的“替换功能”外,还可以用函数来完成。常用的文本替换函数为substitute函数和replace函数。本篇我们讲解substitute函数,下一篇讲解replace函数。
substitute函数的基本语法结构是:
语法结构:substitute(text, old_text, new_text, instance_num)
Text:表示需要替换其中字符的文本,或对含有文本的单元格的引用
Old_text:表示需要替换的就文本
New_text:用于替换Old_text的文本
Instance_num:可选。用来指定要替换第几个出现的Old_text
substitute函数用于在文本字符串中用new_text来代替old_text。让我们用几个例子来帮助我们理解这个函数的用法吧。
例一:去除空格
在下面这个例子中,我们要将“我 爱 学 习E X C EL!”中的空格去除掉。在C2单元格中输入“=SUBSTITUTE(A3," ","")”就可以了。
这里用空字符代替了字符串中的空格。
例二:统计报名人数
有时候,花名册是书写成这个样子的。怎样快速统计人数呢?
分析一下:报名人数彼标点符号“,”多1,因此只要求出有多少个标点符号就能算出有多少报名人数。同时,按照中文的输入方式,在逗号的后面会有一个空格用于分隔字符。因此输入下面的公式“=LEN(B10)-LEN(SUBSTITUTE(B10,",",""))+1”,就可以算出人数了。
首先用len函数求出字符串的总长度。
用“”来代替“,”后,此时字符串中已经没有标点符号了。
用len函数来计算替换后的字符串的长度。
总长度减去替换后的长度。其实就是逗号的个数。
逗号个数加一就是报名人数。
例三:分列
我们经常会遇到这样的情况:需要将数字和文本混合书写在同一单元格内的字符串分列。下面的情况是不适合用Excel的分列功能的,因为文本字符的长度不一致。
此我们可以用substitute函数来实现这样的效果。这里需要用到数组公式。
在单元格C17中我们输入
“=LEFT(A17,SUM(LEN(A17)-LEN(SUBSTITUTE(A17,{0,1,2,3,4,5,6,7,8,9},""))))”,并三键回车
在单元格D17中我们输入“=RIGHT(A17,LEN(A17)-LEN(C17))”
它基本的逻辑是:
用空字符分别替代字符串中的数字0~9。
分别计算替代后的新的字符串的长度。
用替代前的字符串的长度减去替代后的字符串的长度,实际得出的是每个数字的个数。
将以上这些数字的个数相加,其和就是字符串中数字的位数。
用left函数将数字从字符串中提取出来。
最后用right函数将文本提取出来。
substitute函数这个分列的应用是比较复杂的。大家如有问题请私信我哦!
-END-
欢迎关注【Excel应用之家】专栏,了解更多的Excel实际应用技能,尽在Excel应用之家!
版权声明:本文归Excel应用之家专栏(微信公众号Excel应用之家)原创撰写,未经允许不得转载。欢迎关注专栏/公众号。