【原】Excel 函数公式 字符替换函数哥俩好REPLACE和SUBSTITUTE

快速浏览

往期合集：【2023年3月】【2023年4月】【2023年5月】【2023年6月】

实用案例：|收费管理系统|中医诊所收费系统|日期控件|简单的收发存|电子发票管理助手|Excel表格拆分神器|

内容提要

• 字符替换函数：REPLACE
• 字符替换函数：SUBSTITUTE

大家好，我是冷水泡茶，前两天我分享了字符串提取函数LEFT/MIN/RIGHT，在举例中使用了SUBSTITUTE字符串替换函数，大家可能知道，EXCEL中还有一个字符串函数REPLACE，它们怎么用呢？有什么区别呢？下面我们就一起来一探究竟吧

REPLACE函数

语法：

REPLACE(old_text,start_num, num_chars,new_text)

参数：

old_text：必需。要替换其部分字符的文本。

start_num：必需。old_text中要替换为new_text的字符位置。

num_chars：必需。old_text中希望REPLACE使用new_text来进行替换的字符数。

new_text：必需。将替换old_text中字符的文本。

说人话就是：在一个字符串中，从指定位置开始，把指定数量的字符替换成新的字符。

经典应用：用公式取得EXCEL工作表名

很早以前我们分享过案例：EXCEL取工作表名，今天再重温一下，如何取得工作表名：

1、定义名称sheetname

=REPLACE(GET.DOCUMENT(1),1,FIND("]",GET.DOCUMENT(1)),)&T(NOW())

（1）通过宏表函数GET.DOCUMENT(1)来取得工作表名称，但它包含了工作簿的信息，如：[工作簿1]Sheet1，而我们只想要Sheet1

（2）我们分析它的字符特征，在Sheet1前有一个方括号"]"，我们用FIND函数来取得方括号的位置。FIND("]",GET.DOCUMENT(1))

（3）这样，我们就用REPLACE函数，把GET.OCUMENT(1)取得的完整的工作表名，从其第一个字符开始始，到“]“之间的字符替换为空，这样就取得了工作表名。

（4）在公式后面接上T(NOW()),返回一个空字符""，实现函数实时更新的功能。

（5）宏表函数，这个我们平时用得不多，稍微了解一下即可，当然，它们也有着非常强大的功能，这不是今天的重点，而且它们不能在工作表中直接使用。

2、在工作表中输入公式=sheetname，猜猜会发生什么？没错，工作表的名称妥妥地出现在单元格里。

3、也许有人会说，这有什么用呢？这么大费周章地。要是有人这么问，那你可能没有做过这样一类表格，比如工作表的标题叫“江苏区域销售月报表”，工作表的名称却叫“山东区域”，有点对不上号啊，可能是这位复制了“江苏区域”这张表，把工作表名称改成了“山东区域”，但工作表的标题却没有改。要是在全公司开会时作报告，出现这样的失误，你说尴尬不尴尬？

4、解决办法就是用我们的定义名称sheetname，在工作表的标题设置公式=sheetname&"销售月报表"，我们就再也不用担心标题不对啦。只要我们改了工作表名，表头自动跟着变化，就一个字，爽！

SUBSTITUTE函数

语法:

SUBSTITUTE(text, old_text, new_text, [instance_num])

参数：

text：必需。需要替换其中字符的文本，或对含有文本（需要替换其中字符）的单元格的引用。

old_text：必需。需要替换的文本。

new_text：必需。用于替换 old_text 的文本。

Instance_num：可选。指定要将第几个 old_text 替换为 new_text。如果指定了 instance_num，则只有满足要求的 old_text 被替换。否则，文本中出现的所有 old_text 都会更改为 new_text。

换句话说，就是在一个字符串中，把旧的文本替换成新的文本，如果指定了第几个旧文件，则只有满足条件的文本才会被替换。如果没指定第几个，则所有符合条件的旧文本都会被替换成新的文本。

经典应用：用公式取得末级科目名称

就用我们上期Excel文本字符提取函数三剑客LEFT、MID、RIGHT经典应用详解中的例子：

文本（B3单元格）：库存现金\人民币1\人民币2\人民币3\人民币4

需求：我们要取得末级科目“人民币4”

公式：

=RIGHT(B3, LEN(B3)-  FIND("@",SUBSTITUTE(B3,"\", "@",LEN(B3)-  LEN(SUBSTITUTE(B3,"\","")))))

（1）LEN(B3)-  LEN(SUBSTITUTE(B3,"\",""))，计算最后一个“\”的序号（即第几个），设为P

（2）以下公式

SUBSTITUTE(B3,"\", "@",LEN(B3)-  LEN(SUBSTITUTE(B3,"\","")))

可以变成

SUBSTITUTE(B3,"\", "@",p)

表示把第P个，即最后一个“\”替换成“@”,这样原来的文本就变成了：库存现金\人民币1\人民币2\人民币3@人民币4

（3）接着用FIND函数，找出“@”的位置Q

（4）最后，我们用RIGHT函数：RIGHT(B3,LEN(B3)-Q)得到需要的结果。

总结

如果需要在某一文本字符串中替换特定位置处的任意文本，可以使用 REPLACE函数；

如果需要在某一文本字符串中替换指定的文本，可以使用 SUBSTITUTE函数。

你有哪些精妙的用法呢？欢迎在评论留言。

正文完