10.5 替换字符或字符串

Excel 中除了使用查找替换功能批量替换字符外，还可以使用文本替换类函数将字符串中的部分或全部内容替换成新的字符串。文本替换类函数包括SUBSTITUTE函数、REPLACE函数，以及用于区分双字节字符的REPLACEB函数。

一、 字符替换函数SUBSTITUTE

SUBSTITUTE函数用于将字符串中指定的字符替换为新的文本字符串。函数语法如下SUBSTITUTE(text,old text,new_text,[instance_num])

第一参数text是必需参数，为需要替换其中字符的原始文本或单元格引用。

第二参数old_text是必需参数，为需要被替换的“旧文本”。如果第一参数的字符串中入包含该参数的字符串，则返回原始文本。

第三参数new_text是必需参数，为用于替换的“新文本”。如果该参数为空文本或省路参数的值仅保留参数之前的逗号时，相当于将需要替换的“旧文本”删除。

第四参数instance_num是可选参数，表示替换第几个“旧文本”。如果省略该参数，所有“旧文本”都会被替换。示例如下：

例：使用SUBSTITUTE函数计算平均分

上图中，B列的数据记录不规范，有的单元格中仅包含数字，有的单元格最后包含“分”字，需要在D2单元格中计算平均分。

在D2单元格中输入以下数组公式，按<Ctrl+Shift+Enter>组合键。

{ =ROUND(AVERAGE(--SUBSTITUTE(B2:B7,"分",)),2)}

公式中SUBSTITUTE函数的第三参数省略，表示将B2:B7单元格中的“分”字替换为空。不包含“分”字的单元格将不受影响，返回原有的内容。使用“--”(两个负号)将SUBSTITUTE函数的结果由文本转换为数值，再由AVERAGE函数进行求平均数，用ROUND函数保留二位小数。

提示：

本例仅作为SUBSTITUTE函数的一项使用方法说明，不代表所有不规范的数据都能够通过函数的方法完成计算。实际输入数据时可将不同类别的数据单独一列存放，数值后面不加文本。如果使用类似“1箱54只”“3包22个”的数据输入形式，将对后续的汇总带来极大的麻烦。

例：使用SUBSTITUTE函数计算部门人数

下图为某单位人员名单的部分内容，B列中每个单元格中有多个人员编号，中间用半角逗号作为分隔。需要在C列计算每个部门的人数。

在C2单元格中输入以下公式，向下复制到C6单元格。

=LEN(B2)-LEN(SUBSTITUTE(B2，”,"，))+1

公式第一部分LEN(B2)用于计算B2单元格中的字符数。

公式第二部分先用SUBSTITUTE函数将B2单元格中的逗号“,”替换为空文本，等于将所有逗号“,”从原始文本中删除，再用LEN函数计算替换后的文本字符数。

最后用第一部分的原始字符数减去逗号“,”被删数后的字符数，即得到原始字符串中一共有几个逗号“,”。由于每个单元格中的人数总是比逗号数量多一个，因此，最后再在公式中加1，即得到每个部门的人数。

例：借助SUBSTITUTE函数提取产品批号中的工厂码

下图为某公司产品的批号，其格式为字母数字的组合，中间用“_”分隔不同的信息。其中第二个和第三个“_”之间的内容为产品的工厂码，需要将其提取至B列。

在B2单元格中输入以下公式，向下复制到B5单元格。

=TRIM(MID(SUBSTITUTE(A2,"-",REPT(” ",99)),99*2,99))

REPT函数将指定文本重复多次组成字符串，基本语法如下。

REPT(text,number_times)

第一参数text为需要重复的内容，可以是字符串或单元格引用

第二参数number_times为将第一参数重复的次数。

公式中REPT(“ "99)的作用是生成99个空格。

用SUBSTITUTE函数将A2单元格中的“_”替换为99个空格。这里用99个空格的目的是将原始字符中的各段文本用足够多的空格分开构成新的字符串。新的字符串相当于如下。

DSF (99个空格) 25 (99个空格) D24 (99个空格) 50

因为要提取的字符在原始文本的第3段，所以用MID函数在以上字符串中第99*2个字符开始，提取99个字符，返回的结果相当于如下字符串。

(9个空格) D24 (87个空格)

最后再用TRIM函数清除文本两端多余的空格，即得到需要的工厂码。

二、用REPLACE函数替换字符串

REPLACE函数用于将部分文本字符串替换为新的字符串，与SUBSTITUTE函数的区别是，SUBSTITUTE函数是针对字符串中的指定字符内容进行替换，REPLACE函数是针对符串中的指定字符位置进行替换，其语法如下:

REPLACE(old_text,start_num,num_chars,new_text)

第一参数old_text表示要替换其部分字符的源文本。

第二参数start_num指定源文本中要替换为新字符的位置。

第三参数num_chars表示使用新字符串替换源字符串中的字符数，如果该参数为0或省略参数值，可以实现类似插入字符(串)的功能。

第四参数new_text表示用于替换源文本中字符的文本。

例：使用REPLACE函数隐藏手机号码中间4位

在下图中需要将B列手机号码中间4位数字用星号“****”隐藏。

在C2单元格中输入以下公式，向下复制到C10单元格。

=REPLACE(B2，4，4,”****”)

公式中使用REPLACE函数从B2单元格的第4个字符起，将4个字符替换为“****”

例：使用REPLACE函数将银行卡号分段显示

下图为某单位客户收款的银行卡号，为便于读取和核对，需要将银行卡号数字分段显示。

在B2单元格中输入以下公式，向下复制到B10单元格.

=REPLACE(REPLACE(REPLACE(REPLACE(A2,5,0," "),10,0," "),15,0," "),20,0," ")

REPLACE函数第三参数为0表示插入字符。公式中使用了四次REPLACE函数，第一次先用REPLACE函数在A2单元格第5个字符前插入一个空格。生成的新字符串再作为第二个REPLACE函数的第一参数，在新字符串的第10个字符前再次插入一个空格。依此类推，形成每四个数字为一组的分段显示方式。

三、用CLEAN函数和TRIM函数清理非打印字符和多余空格

部分从网页、ERP系统或从其他软件中导出的文本会存在一些非打印字符，影响正常数据查找及汇总计算。此外，由于数据录入时的疏忽，可能会在英文单词或中文姓名之间入多个空格。

CLEAN函数用于删除文本中的部分非打印字符(ASCII码的值为0~31)

使用TRIM函数能够清除文本中除了单词之间的单个空格外的所有空格。

它们的语法别为:

CLEAN(text)

TRIM(text)

四、使用NUMBERVALUE 函数转换不规范数字

在整理表格数据的过程中，经常会有一些不规范的数字影响数据的汇总分析。例如，在数字中混有空格，或者夹杂有全角数字及文本型数字等。对于文本数据，可以使用TRIM函数清理多余空格，使用ASC函数将全角字符转换为半角字符。对于数字内容，使用NUMBERVALUE函数可以兼容以上两种功能。

NUMBERVALUE函数较VALUE函数在功能上有一定的提升。该函数不仅可以实现VALUE函数日期转换为数值序列、文本型数字转换为数值型数字、全角数字转换为半角数字等功能，还可以处理混杂空格的数值及符号混乱等特殊情况。

对于“7430%”这样的数据，NUMBERVALUE函数能够将其转换为74.3而使用VALUE函数，则返回错误值#VALUE!。