使用 SUBSTITUTE 替换文本

数据经常需要在公式中执行查找和替换：去掉电话号码中的连字符、替换某个单词，或删除多余的符号。

SUBSTITUTE 函数会在单元格中查找一段文本，并将每个出现项替换为新文本，而且不会更改单元格原本的内容。

它相当于基于公式的“查找和替换”，但会随着数据变化自动更新。

SUBSTITUTE 最多接受四个参数：

• 文本 要处理的原始字符串或单元格
• 旧文本 要查找的文本
• 新文本 要替换成的文本
• 实例编号 （可选）要替换的是第几个出现项

其形式为 =SUBSTITUTE(text, old_text, new_text, [instance_num])。如果省略最后一个参数，就会替换所有出现项。

一个常见技巧是将某个内容替换为空内容，从而将其删除。具体做法是将新文本设为空字符串 ""。

如果 A2 中存储的是 555-123-4567，下面的公式会删除所有连字符，得到 5551234567。

这种模式适用于清理电话号码、ID 以及其他带标点的值。

SUBSTITUTE 也可以处理完整的单词，而不仅是单个字符。

如果 A2 中存储的是 年度报告 2023，您可以通过替换单词来更新年份：

结果是 年度报告 2024。单元格中每个匹配的出现项都会被更改。

重要提示：SUBSTITUTE 会完全匹配文本，包括大小写。

如果 A2 中存储的是 猫 猫 CAT，那么 =SUBSTITUTE(A2, "cat", "dog") 只会更改小写的 猫，结果为 猫 狗 CAT。

如果需要执行不区分大小写的替换，请先使用 LOWER 或 UPPER 统一大小写，或者在表格的下一课中使用正则表达式函数。

可选的第四个参数实例编号可以让您指定一个出现项，而不是替换所有出现项。

如果 A2 中存储的是 a-b-c-d，并且您只想更改第二个连字符，请将实例编号设置为 2。

结果是 a-b/c-d：只有第二个连字符变成了斜杠。

假设导入的金额以文本形式存储为 $1,250.00。要将其转换为可用的数字，您必须去掉美元符号和逗号。

将两个 SUBSTITUTE 调用彼此嵌套，以删除这两个字符。

内部调用删除逗号，外部调用删除美元符号，最后得到 1250.00。

您可以根据需要嵌套任意多个 SUBSTITUTE 调用，每个调用清理一种不同的字符。

要将包含空格、点号和斜杠的混乱代码统一为使用连字符的格式，请串联三个替换操作。

每一层都会将结果传给下一层，逐步构建出最终的清理后字符串。

不要将 SUBSTITUTE 与类似的 REPLACE 函数混淆。

• SUBSTITUTE 通过匹配内容来替换文本（查找这个单词，然后替换它）。
• REPLACE 通过位置来替换文本（从第 5 个位置开始替换 4 个字符）。

当您知道要查找什么时使用 SUBSTITUTE，当您知道它位于哪里时使用 REPLACE。

一个巧妙的额外技巧是将 SUBSTITUTE 与 LEN 结合起来，统计某个内容出现了多少次。

用删除某个字符后的长度减去原始长度。如果 A2 中存储的是 a,b,c,d，此公式会统计逗号的数量，结果为 3。

这个技巧非常适合统计分隔列表中的项目数量。

假设 A2 中存储的是带点号和空格的混乱号码，例如 555.123 4567。您希望将其统一为带连字符的格式 555-123-4567。

首先去掉点号和空格，然后可以重新插入连字符；目前只需将分隔符统一为连字符即可。

嵌套替换操作可以统一各种混乱的分隔符。

请检验您对 SUBSTITUTE 的理解。

您学会了如何在公式中查找和替换文本：

• =SUBSTITUTE(text, old_text, new_text, [instance_num])
• 将 "" 用作新文本可以删除字符。
• 它区分大小写；如有需要，请先统一大小写。
• 可选的实例编号可以指定一个出现项。
• 嵌套多个调用可以清理多种字符；与 LEN 结合可以统计出现次数。

您已经完成“连接和更改文本”课程。祝贺您构建出了干净、一致的数据！