3.2 文本新旧替换的实例
函数5：REPLACE函数（替换文本字符串中的部分文本）
函数功能
REPLACE函数使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。无论默认语言设置如何，函数REPLACE始终将每个字符（不管是单字节还是双字节）按1计数。
函数语法
REPLACE(old_text, start_num, num_chars, new_text)
参数解释
- old_text：必需。表示要替换其部分字符的文本。
- start_num：必需。表示要用new_text替换的old_text中字符的位置。
- num_chars：必需。表示希望REPLACE使用new_text替换old_text中字符的个数。
- new_text：必需。表示将用于替换old_text中字符的文本。
用法剖析
实例解析
实例113 屏蔽中奖手机号码的后几位数
使用REPLACE函数可以实现屏蔽重要号码的后几位数，将其设置以“*”显示，以达到保护客户隐私的目的。
选中C2单元格，在公式编辑栏中输入公式：
按Enter键即可得到第一位客户的屏蔽号码。
将鼠标指针指向C2单元格的右下角，向下复制公式，即可快速得到其他客户屏蔽后的电话号码，如图3-19所示。
图3-19
公式解析
=REPLACE(B2,8,4,"****")
从B2单元格中的第8位开始，将剩下的后四位替换为“****”。
实例114 快速更改产品名称的格式
下面表格的“品名规格”列的写法格式中使用了下画线，现在想批量替换为“*”号。
选中C2单元格，在公式编辑栏中输入公式：
按Enter键即可得到需要的显示格式，如图3-20所示。
将鼠标指针指向C2单元格的右下角，向下复制公式，即可实现格式的批量转换。
图3-20
公式解析
=REPLACE (A2,5,1,"*")
使用REPLACE函数从第5位开始替换，共替换1个字节，替换为“*”符号。
提示
与REPLACE用法类似的还有REPLACEB。REPLACEB函数查找时是按字节进行计算的。
函数6：SUBSTITUTE函数（用新文本替换旧文本）
函数功能
SUBSTITUTE函数用于在文本字符串中用新文本替代旧文本。
函数语法
SUBSTITUTE(text,old_text,new_text,instance_num)
参数解释
- text：必需。表示需要替换其中字符的文本，或对含有文本的单元格的引用。
- old_text：必需。表示需要替换的旧文本。
- new_text：必需。用于替换old_text的新文本。
- instance_num：可选。用来指定要以new_text替换第几次出现的old_text。如果指定了instance_num，则只有满足要求的old_text被替换；否则会将text中出现的每一处old_text都更改为new_text。
用法剖析
实例解析
实例115 去除文本中多余的空格
如果表格中的文本输入的不规范或者是复制的文本，有时候会存在很多空格。使用SUBSTITUTE函数可以一次性删除其中的空格，得到结构紧凑的文本内容显示。
选中B2单元格，在公式编辑栏中输入公式：
按Enter键即可返回无空格文本显示。
将鼠标指针指向B2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可完成所有空格的删除，并得到正确格式显示的文本，如图3-21所示。
图3-21
提示
注意第一个参数双引号中有一个空格，第二个参数双引号中无内容。
实例116 格式化公司名称
在A列中显示的是复合公司名称，包括公司地区、名称和代表人员。这里可以使用SUBSTITUTE函数实现将第二个“-”连接符更改为“:”，并删除第一个连接符。
选中B2单元格，在公式编辑栏中输入公式：
按Enter键即可替换A2单元格中的第二个连接符为“:”。
将鼠标指针指向B2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可完成所有连接符的替换和删除，如图3-22所示。
图3-22
公式解析
① 使用REPLACE函数将A2单元格中的第一个“-”符号替换为空。
② 使用SUBSTITUTE函数将剩下的“-”符号替换为“∶”。
实例117 计算各项课程的实际参加人数
如图3-23所示的表格中在统计各个舞种报名的学员时写成了C列中的数据。要求将实际人数统计出来。
图3-23
选中D2单元格，在公式编辑栏中输入公式：
按Enter键即可统计出B2单元格中最终报名人员的数量。
将鼠标指针指向D2单元格的右下角，待光标变成十字形状后，按住鼠标左键向下拖动进行公式填充，即可得到所有课程的实际人数，如图3-24所示。
图3-24
嵌套函数
LEN函数属于文本函数类型，用于统计出给定文本字符串的字符数。
公式解析
① 统计C2单元格中字符串的长度。
② 将C2单元格中的逗号替换为空。
③ 统计取消了逗号后C2单元格中字符串的长度。
④ ①步结果与③步结果相减为逗号数量，逗号数量加1为姓名的数量。
提示
本例中巧妙运用了统计逗号数量的方法来变相统计人数，人数为逗号数量加1。
实例118 查找特定文本且将第一次出现的删除，其他保留
如图3-25所示，想将B列中的数据替换为D列中的形式，如果使用公式“=SUBSTITUTE(B2,C2,)”，则替换后的结果如图3-26所示，达不到目的。
图3-25
图3-26
此时可以按如下方法来设置公式。
选中D2单元格，在公式编辑栏中输入公式：
按Enter键可以看到B2单元格中的数据只有第一个“04”被替换了，第二个“04”被保留，如图3-27所示。
图3-27
选中D2单元格，拖动右下角的填充柄向下复制公式，即可实现批量替换。
公式解析
① 将C2中的字符与“-”相连接。
② 使用空白字符（两个逗号间无任何字符表示空白）替换①步的返回值，最后一个参数用来指定以新文本替换第几次出现的旧文本，即本例要求的只替换第一次出现的目标文本。