计算机公式SUBSTITUTE,SUBSTITUTE函数详解_Excel公式教程

【语法】SUBSTITUTE(text,old_text,new_text,[instance_num])

把文本text中的子文本old_text，用新文本new_text替换。

SUBSTITUTE函数不支持通配符，参数text、old_text和new_text中的“?”“*”“~”一律视作普通字符。

SUBSTITUTE函数严格区分英文字母大小写。

参数instance_num是一个数字值，小数部分截尾取整。用来指定替换文本text中第几次出现的old_text。

Instance_num必须≥1，否则函数返回错误值#VALUE!。

如果指定了instance_num，则只替换该处的old_text；否则替换所有的old_text。

如果instance_num大于old_text出现的次数，则不发生替换，函数直接返回原文本text。

【用法】

一、替换文本。

=SUBSTITUTE("A类","A","B") 返回"B类"

=SUBSTITUTE("1-2-3-4","-","_") 返回"1_2_3_4"

=SUBSTITUTE("1-2-3-4","-","#",3) 返回"1-2-3#4"，只替换第三个“-”

=SUBSTITUTE("111111","11","22",3) 返回"112211"，而不是"111122"

二、把new_text指定为空文本或空参数，即把old_text替换为空文本，相当于删除文本old_text。例如：

=SUBSTITUTE("河南省开封","省",) 返回"河南开封"

如果old_text为空文本或空参数，则不发生替换。

三、解决字母大小写混杂的问题。

如图，要把A列的“A类”和“a类”都替换为“B类”。可以在单元格B1输入以下其中一个公式，然后把公式向下复制就可以了：

=SUBSTITUTE(UPPER(A1),"A","B") 或

=SUBSTITUTE(LOWER(A1),"a","B") 或

=SUBSTITUTE(SUBSTITUTE(A1,"a","B"),"A","B")

四、结合LEN函数，统计文本中包含某特征文本的数目。

假设单元格A1是文本“12,213,34”，下面的公式返回A1中包含数字的个数，返回3：

=LEN(A1)-LEN(SUBSTITUTE(A1,",",))+1

公式中利用SUBSTITUTE(A1,",",)把文本里的逗号删掉，然后用LEN函数对删掉逗号前和删掉逗号后的文本字符数进行比较，即可知道文本有几个逗号。

五、结合FIND、MID、REPLACE等函数，提取文本中的一段字符串。

假设单元格A1是文本“12,213,34,52”，下面的公式提取其中第三个数字，返回"34"：

=MID(A1,FIND("_",SUBSTITUTE(A1,",","_",2))+1,FIND("_",SUBSTITUTE(A1,",","_",3))-FIND("_",

SUBSTITUTE(A1,",","_",2))-1)

文本中的第三个数字，就是第二、第三个逗号之间的字符。公式中利用SUBSTITUTE(A1,",","_",2)把文本里第二个逗号改为下划线，然后用FIND查找下划线，就能确定第二逗号的位置。

同样地，用FIND("_",SUBSTITUTE(A1,",","_",3))确定第三个逗号的位置。

改一下思路，把提取第二、第三个逗号之间的字符，改为删掉第二个逗号之前、第三个逗号之后的字符。公式如下：

=REPLACE(REPLACE(A1,FIND("_",SUBSTITUTE(A1,",","_",3)),99,),1,FIND("_",SUBSTITUTE(A1,",",

"_",2)),)

公式中，先用里层的REPLACE把第三个逗号及其后面的字符删掉，再用外层的REPLACE把第二个逗号及其前面的字符删掉。