在 Excel 中,一共有两个替换字符的函数,一个是Substitute函数,另一个是Replace函数;前者是用新字符替换旧字符,后者是用新字符替换指定字符数。Substitute函数既可以替换源文本中所有指定字符,又可以替换某个指定字符,并且还能嵌套使用以实现一次替换多个不同字符。以下是 Excel Substitute函数的使用方法,共包含5个实例,实例操作所用版本均为 Excel 。
一、Substitute函数语法
1、表达式:SUBSTITUTE(Text, Old_Text, New_Text, [Instance_Num])
中文表达式:Substitute(文本, 被替换文本,替换文本,[被替换文本序号])
2、说明:
Instance_Num 为可选参数,用于指定替换第几个 Old_Text,如果省略,则替换所有 Old_Text。另外,Substitute函数不支持通配符,例如星号 "*" 不是代表任意个字符,只代表星号本身。
二、Substitute函数的使用方法及实例
(一)省略 Instance_Num 参数一次替换多个相同字符
1、假如要把“excel替换字母,数字,汉字”中的半角逗号(,)替换为全角逗号(,)。选中 B1 单元格,输入公式 =substitute(a1,",",","),按回车,则“excel替换字母,数字,汉字”中所有半角逗号被替换为全角逗号;操作过程步骤,如图1所示:
图1
2、公式说明:公式 =substitute(a1,",",",") 省略了 Instance_Num 参数,则会用 New_Text(即全角逗号) 替换文本中所有 Old_Text(即半角逗号)。
(二)指定 Instance_Num 参数一次仅替换一个字符
1、假如要替换 ExcEl 中第二个大写 E 为小写。选中 B1 单元格,把公式 =SUBSTITUTE(A1,"E","e",2) 复制到 B1,按回车,返回 Excel ;操作过程步骤,如图2所示:
图2
2、公式把 Instance_Num 参数设置为 2,即用小写 e 替换 ExcEl 中的第二个大写 E。
(三)只替换单个数字
1、假如有一串数字 25,10,5,16,51,要把里面的单个 5 替换掉,不能替换其它数字中的 5。选中 B1 单元格,把公式 =SUBSTITUTE(A1,",5,",",") 复制到 B1,按回车,则 5 被替换掉,操作过程步骤,如图3所示:
图3
2、公式说明:公式 =SUBSTITUTE(A1,",5,",",") 把 Old_Text 参数设置为 ",5,",主要是为了区别其它数字(如 25)中的 5,以避免把 25 中的 5 也替换掉;把 New_Text 设置为 ",",用于替换掉 5 后把 5 的前后数字分隔。
(四)Substitute函数嵌套使用,同时替换多个不同字符
1、替换通配符星号 * 和井号 #
A、假如要替换 25,*,10,#,51 中的 * 和 #。 选中 B1 单元格,把公式 =SUBSTITUTE(SUBSTITUTE(A1,"*,", ""),"#,","") 复制到 B1,如图4所示:
B、按回车,则 25,*,10,#,51 中 * 和 # 被替换掉,如图5所示:
图5
C、公式说明:公式 =SUBSTITUTE(SUBSTITUTE(A1,"*,", ""),"#,","") 由两个 Substitute函数嵌套组成;里面的 SUBSTITUTE(A1,"~*", ",") 用于替换 *;外面的 Substitute函数用于替换 #,它的 Text 参数是替换 * 后的结果,即 SUBSTITUTE(A1,"*,", "") 返回的结果。
2、替换双引号
A、假如要把数字串 “25”,“10”,“51” 中的双引号去掉。选中 B1 单元格,把公式 =SUBSTITUTE(SUBSTITUTE(A1,"“", ""),"”","") 复制到 B1,按回车,则数字串中的左右双引号全被去掉,操作过程步骤,如图6所示:
图6
B、公式说明:公式 =SUBSTITUTE(SUBSTITUTE(A1,"“", ""),"”","") 也由两个 Substitute函数嵌套组成;里面的 SUBSTITUTE(A1,"“", "") 用于去掉左双引号,外面的 Substitute 用于去掉右边的双引号。
三、SubStitute函数与Replace函数的区别
SubStitute函数是用一个(或一串)字符替换另一个字符(或一串),而Replace函数是用一个(或一串)字符替换指定的字符数。如果要替换括号中的一串不确定有多少个的字符,由于SubStitute函数不支持通配符,因此无法实现替换,但用Replace函数却能替换,方法如下:
1、假如要替换文本“excel(kh@ei8#9kdfk8*ksdkjd)”中的括号及其中的字符串为“函数”。把公式 =REPLACE(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1,"函数") 复制到 B1 单元格,如图7所示:
图7
2、按回车,则源文本中的括号及其中的字符串被替换为“函数”,返回 excel函数,如图8所示:
图8
3、公式说明:
1、公式 =REPLACE(A1,FIND("(",A1),FIND(")",A1)-FIND("(",A1)+1,"函数") 由Replace函数和Find函数组成,Find函数用于找出左右括号在文本中的位置,Replace函数用于替换。
2、第一个 FIND("(",A1) 用于找出左括号在文本中的位置作为 Replace 的“开始替换位置”;FIND(")",A1) 用于找出右括号在文本中的位置,然后用右括号的位置减左括号的位置再加 1 作为 Replace 的“要查找的字符数”。