Excel126 | SUBSTITUTE文本替换函数的使用

问题来源

昨天讲人民币阿拉伯数字转中文大写的公式，最后用两个SUBSTITUTE函数替换“零角”为“零”，“零分”为“整”。今天，就有朋友问韩老师SUBSTITUTE函数的具体用法，那韩老师就来总结一下。

函数简介

功能

在某一文本字符串中替换指定的文本。

语法

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

SUBSTITUTE 函数语法具有下列参数：

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

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

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

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

特点

1、区分大小写和全角半角：当text中没有包含 old_text指定的字符串时，函数结果与text相同；

2、当第三个参数为空文本或是省略，而只保留参数前的逗号时，相当于将 old_text替换；

3、当第四个参数省略，text中与 old_text相同的文本将被替换；

4、如果第四个参数有指定，比如“2”，则只有第二次出现的old_text被替换。

以上四个特点举例如下：

实用举例

举例一：隐藏身份证部分字符

公式：=SUBSTITUTE(D1,'及','合')，把“不及格”统一改为“不合格”：

举例二：隐藏身份证部分字符

比如：火车票上的身份证号码从第11位开始隐藏4位，实现公式：=SUBSTITUTE(C2,MID(C2,11,4),'****')

其中：

text：C2，身份证所在单元格；

old_text:：MID(C2,11,4)，用MID函数从身份证号码的第11位取4位；

new_text:：“****”。

举例三：计算字符串内特定字符的个数

公式为：=LEN(D2)-LEN(SUBSTITUTE(D2,6,))

其中：

LEN(D2)：D2字符串的长度；

LEN(SUBSTITUTE(D2,6,))：替换掉了6以后字符串的长度。

举例四：带单位的数值计算

在F11单元格输入公式：

{=(AVERAGE(--SUBSTITUTE(F2:F10,'分',)))}

（CTRL SHIFT ENTER结束）

其中：

{=--SUBSTITUTE(F2:F10,'分',)}，公式内“--”称为“减负运算”，{=SUBSTITUTE(F2:F10,'分',)}的结果是一串文本，前面加一个“-”，是通过取负数将文本转换成数值，再加一个“-”，即负负得正。

“--减负运算”常用于公式中把文本转换为数字。

举例五：同一单元格内最大值

比如下图表中的员工姓名和业绩挤在一个单元格里，要求统计业绩最大值。

在C2单元格输入公式：

{=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))}，（TRL SHIFT RNTER结束）。

{=ROW($1:$100)}：

返回值是1-100组成的数组{1；2；3；4；5；6；7……98；99；100}

{=SUBSTITUTE(B2,ROW($1:$100),)}：

将B2内的文本依次删除1~100数值以后，返回100组文本组成的数组，如下图：

关于SUBSTITUTE函数的用法参考昨天的文章：Excel | 数据输入不规范，部分带数量单位，怎么计算平均值？。

{=SUBSTITUTE(B2,ROW($1:$100),)<>B2}：

返回值是一组TURE与FALSE组成的100个逻辑值数组，将删除了数字后的文本与B2单元格相对比，如果不等于B2返回TURE,如果等于B2返回FALSE。

{＝(SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100)}：

将得到的一级逻辑值与1~100数值相乘，TURE相当于1，FALSE相当于0,相乘以后得到的结果是一个数组，该数组由100个数值组成，分别是B2单元格中包含的所有数字和0。

最后用MAX函数对上述数组内的数值求最大值。

---