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应用之家）原创撰写，未经允许不得转载。欢迎关注专栏/公众号。