必看！Substitute 函数，字符串替换的魔法棒，你会用吗？

大家好，欢迎来到IT知识分享网。

在 Excel 的函数大家庭里，SUBSTITUTE 函数就像是一位低调却实用的小能手，默默地帮助我们解决各种文本替换的难题。对于刚接触 Excel 函数的新手来说，掌握 SUBSTITUTE 函数，就如同拿到了一把开启数据处理大门的钥匙。接下来，就让我们一起走进 SUBSTITUTE 函数的世界，揭开它神秘的面纱。

Excel 函数新手村：SUBSTITUTE 函数初相识

（一）函数功能

SUBSTITUTE 函数，从名字就能看出它的主要职责 —— 替换。它专门用于在文本字符串中，将指定的子字符串替换成新的子字符串。

比如说，你有一列客户的地址信息，里面所有的 “街道” 都误写成了 “街首”，一个个手动修改太麻烦？这时，SUBSTITUTE 函数就能大显身手，一键帮你把所有的 “街首” 替换为 “街道” ，是不是超级方便！在数据处理过程中，类似这样需要批量替换文本的场景数不胜数，SUBSTITUTE 函数的存在，大大提高了我们的工作效率，让繁琐的数据整理工作变得轻松许多。​

（二）语法结构​

想要用好 SUBSTITUTE 函数，首先得了解它的语法结构，就像学习一门外语要先掌握基本句型一样。

SUBSTITUTE 函数的语法是：

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

这看起来有点复杂，不过别担心，我们一个一个来拆解。

• “text” 指的是要在其中进行替换操作的原始文本字符串，它可以是直接输入的一段文字，也可以是对某个包含文本的单元格的引用；
• “old_text” 很容易理解，就是你想要被替换掉的旧子字符串；
• “new_text” 则是用来替换 “old_text” 的新子字符串；
• 而 “[instance_num]” 是一个可选参数，它是一个数值，作用是指定要替换第几次出现的 “old_text” ，如果省略这个参数，那就意味着将替换所有出现的 “old_text” 。​

（三）参数解析​

text 参数：这个参数是整个替换操作的基础对象。

比如，在公式 “=SUBSTITUTE (“我喜欢吃苹果，苹果很美味”, “苹果”, “香蕉”)” 中，“我喜欢吃苹果，苹果很美味” 就是 text 参数，它明确了我们要对哪段文本进行替换操作。

如果把 text 参数换成单元格引用，比如 A1 单元格里存储着 “今天天气真好” ，那么公式 “=SUBSTITUTE (A1, “真好”, “一般”)” 就会对 A1 单元格中的文本进行替换。​

old_text 参数：它决定了我们要把什么内容替换掉。

还是上面吃水果的例子，“苹果” 就是 old_text，它告诉函数，我们要把文本里的 “苹果” 这个词找出来进行替换。

这里要注意，函数对 old_text 的匹配是区分大小写的，如果你的文本里是 “Apple” ，而你设置的 old_text 是 “apple” ，那是无法匹配替换的。​

new_text 参数：这是替换后的新内容。

接着前面的例子，“香蕉” 就是 new_text，函数会把找到的 “苹果” 都换成 “香蕉” ，最终得到 “我喜欢吃香蕉，香蕉很美味” 的结果。​

instance_num 参数：这个可选参数很有意思，它能让我们更精准地控制替换操作。

假设你的文本是 “苹果，香蕉，苹果，橙子，苹果” ，如果你只想替换第二个 “苹果” ，这时就可以用到 instance_num 参数。

公式 “=SUBSTITUTE (“苹果，香蕉，苹果，橙子，苹果”, “苹果”, “草莓”, 2)” ，这里的 “2” 就是 instance_num 参数的值，表示只替换第二次出现的 “苹果” ，结果就会变成 “苹果，香蕉，草莓，橙子，苹果” 。

如果省略这个参数，那所有的 “苹果” 都会被替换成 “草莓” 。

通过灵活运用 instance_num 参数，我们可以在复杂的文本中实现更细致的替换需求。​

进阶之路：SUBSTITUTE 函数实战应用​

当你对 SUBSTITUTE 函数的基本原理有了一定了解后，接下来就可以踏上实战应用的进阶之路啦。在实际的工作和学习中，SUBSTITUTE 函数有着各种各样的应用场景，掌握一些高级组合技巧，更能让它如虎添翼，帮助我们解决复杂的数据处理任务。​

（一）典型应用场景​

文本替换：

这是 SUBSTITUTE 函数最基础也最常见的应用。

隐藏敏感信息：

在处理包含个人信息的数据时，常常需要对敏感信息进行隐藏处理，比如手机号、身份证号等。

统计文本数量：

利用 SUBSTITUTE 函数和 LEN 函数的组合，可以统计文本中某个特定字符或字符串出现的次数。

（二）高级组合技巧​

与 MID、LEN 函数组合批量替换多个文本：

有时候我们需要在一段文本中，按照特定位置和规则替换多个不同的文本。

与 SUMPRODUCT 函数组合对带单位的数值求和：

在处理财务数据或销售数据时，常常会遇到数据带有单位的情况，比如 “100 元”“200 件” ，要对这些数值进行求和，就需要先去掉单位。

高手修炼：提升与拓展​

当你已经熟练掌握了 SUBSTITUTE 函数的基本应用和实战技巧后，想要更上一层楼，成为真正的数据处理高手，就需要深入了解函数的性能优化、错误处理、与其他函数的对比以及替代方案等方面的知识。

这不仅能让你在使用 SUBSTITUTE 函数时更加得心应手，还能拓宽你的数据处理思路，提升整体的数据处理能力。​

（一）性能优化指南​

合理使用参数：

在使用 SUBSTITUTE 函数时，要根据实际需求准确设置参数。特别是 instance_num 参数，如果不需要指定替换的具体位置，就尽量省略该参数，因为每次指定 instance_num 都需要函数额外进行位置判断，会增加计算量。

例如，在批量替换文本中所有的 “苹果” 为 “香蕉” 时，直接使用 “=SUBSTITUTE (A1,”苹果”,”香蕉”)” 即可，无需设置 instance_num 参数。​

避免不必要的计算：

如果文本内容不会发生变化，就不要在公式中反复使用 SUBSTITUTE 函数进行相同的替换操作。可以先将替换后的结果存储在一个新的单元格区域，后续需要使用时直接引用该区域。

比如，有一份固定的产品信息表，其中产品名称中的错误已经用 SUBSTITUTE 函数修正，就可以将修正后的结果复制粘贴为数值，避免每次打开表格时函数都重新计算。​

减少嵌套层数：

在使用多个 SUBSTITUTE 函数嵌套进行复杂替换时，要尽量简化嵌套结构。

可以尝试将一些重复的替换操作提取出来，单独使用一个 SUBSTITUTE 函数完成，然后再进行其他替换。

例如，原本公式 “=SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A1,”旧内容 1″,”新内容 1″),”旧内容 2″,”新内容 2″),”旧内容 3″,”新内容 3″)” ，如果 “旧内容 1” 和 “旧内容 2” 的替换可以合并为一次操作，就可以优化为 “=SUBSTITUTE (SUBSTITUTE (A1,{“旧内容 1″,”旧内容 2”},{“新内容 1″,”新内容 2″}),”旧内容 3″,”新内容 3”)” ，这样可以减少函数的计算步骤，提高效率 。​

（二）错误与修正​

参数错误：

当输入的参数数量不正确或者参数类型不匹配时，会出现错误。

比如，少输入了 new_text 参数，公式 “=SUBSTITUTE (A1,”旧文本”)” ，Excel 会提示 “参数太少” 的错误。解决方法就是按照函数语法，正确输入所有必需参数。

另外，如果 text 参数本应是文本类型，却输入了数值，也会导致错误。例如 “=SUBSTITUTE (123,”1″,”2″)” ，此时需要将数值转换为文本，可以使用 TEXT 函数，如 “=SUBSTITUTE (TEXT (123,”0″),”1″,”2″)” 。​

找不到旧文本：

如果在文本中找不到要替换的 old_text，函数不会报错，但也不会进行替换操作。

例如，在公式 “=SUBSTITUTE (“我喜欢吃苹果”,”香蕉”,”橙子”)” 中，因为原文本中没有 “香蕉” ，所以结果还是 “我喜欢吃苹果” 。

如果需要确认是否进行了替换操作，可以结合 IF 函数和 LEN 函数进行判断，公式 “=IF (LEN (SUBSTITUTE (A1,”旧文本”,”新文本”))=LEN (A1),”未找到旧文本，未替换”,”已替换”)” 。​

数据类型不匹配：

在与其他函数组合使用时，可能会因为数据类型不一致导致错误。

比如在与数值计算函数组合时，如果 SUBSTITUTE 函数返回的是文本格式的数据，而计算函数要求数值类型，就会出错。

例如 “=SUM (SUBSTITUTE (B2:B10,”元”,””))” ，这里 SUBSTITUTE 函数去掉 “元” 后返回的是文本，不能直接用于 SUM 求和。解决办法是在 SUBSTITUTE 函数外面乘以 1，将文本转换为数值，即 “=SUM (SUBSTITUTE (B2:B10,” 元 “,””)*1)” 。​

（三）函数对比​

与 REPLACE 函数对比：

SUBSTITUTE 函数和 REPLACE 函数都有文本替换的功能，但它们的侧重点不同。

SUBSTITUTE 函数是根据文本内容进行替换，只要指定要替换的旧文本和新文本即可 。

例如 “=SUBSTITUTE (“我要去北京旅游”,”北京”,”上海”)” ，将 “北京” 替换为 “上海” 。而 REPLACE 函数是按照字符位置进行替换，需要指定从第几个字符开始替换，替换几个字符以及新文本。

比如 “=REPLACE (“我要去北京旅游”,4,2,”上海”)” ，从第 4 个字符开始，替换 2 个字符（即 “北京”）为 “上海” 。所以，当你明确知道要替换的文本内容时，优先使用 SUBSTITUTE 函数；当你只知道要替换的字符位置和长度时，就选择 REPLACE 函数。​

与 SUBSTITUTES 函数对比（WPS 特有）：

如果你使用的是 WPS 表格，会发现有一个 SUBSTITUTES 函数，它和 SUBSTITUTE 函数很相似，但功能更强大。

SUBSTITUTE 函数一次只能替换一种旧文本为新文本，而 SUBSTITUTES 函数可以实现批量替换。

例如，有一段文本 “红色苹果，黄色香蕉，黑色葡萄” ，要同时将 “红色” 替换为 “Red” ，“黄色” 替换为 “Yellow” ，“黑色” 替换为 “Black” ，使用 SUBSTITUTE 函数需要多次嵌套，而 SUBSTITUTES 函数只需一个公式 “=SUBSTITUTES (A1,{“红色”,”黄色”,”黑色”},{“Red”,”Yellow”,”Black”})” 就能完成，大大提高了替换效率 。​

（四）替代方案​

查找替换功能：

对于简单的文本替换需求，Excel 自带的查找替换功能（快捷键 Ctrl + H）也能完成。

比如，要将表格中所有的 “电脑” 替换为 “计算机” ，通过查找替换对话框，输入查找内容 “电脑” 和替换为 “计算机” ，点击全部替换即可。它的优点是操作简单直观，不需要编写公式；缺点是只能进行单一的替换操作，对于复杂的替换需求，如根据条件替换或者与其他函数配合使用，就无法实现。​

VBA 宏：

如果你对 VBA 编程有一定了解，也可以使用 VBA 宏来实现文本替换功能。VBA 宏可以根据复杂的逻辑和条件进行文本替换，灵活性很高。

例如，要根据不同的条件替换不同的文本，使用 VBA 宏可以编写如下代码：​

Sub ReplaceText()​ Dim cell As Range​ For Each cell In Range("A1:A10") '假设要处理A1到A10单元格区域​ If cell.Value Like "*苹果*" Then​ cell.Value = Replace(cell.Value, "苹果", "香蕉")​ ElseIf cell.Value Like "*橘子*" Then​ cell.Value = Replace(cell.Value, "橘子", "橙子")​ End If​ Next cell​ End Sub​

​VBA 宏的优势在于可以实现高度自定义的替换操作，但编写和调试代码需要一定的编程基础和时间成本。​

3. Power Query：

Power Query 是 Excel 的一个强大的数据处理插件，它也能轻松实现文本替换。

将数据导入 Power Query 编辑器后，通过 “替换值” 功能，可以方便地对指定列的数据进行替换。而且 Power Query 支持对数据进行多次转换和清洗操作，处理后的数据可以直接加载回 Excel 表格中。

例如，有一份包含大量文本数据的表格，需要进行多种复杂的文本替换和数据整理，使用 Power Query 可以将替换操作和其他数据处理步骤整合在一起，形成一个完整的数据处理流程，并且可以随时刷新数据以应用最新的替换规则 。​

总结回顾，展望未来​

通过对 SUBSTITUTE 函数的全面学习，我们从新手村出发，认识了它的基本功能、语法结构和参数解析，接着在进阶之路上，将其应用于各种实际场景，并掌握了高级组合技巧，最后在高手修炼阶段，深入了解了函数的性能优化、错误处理、与其他函数的对比以及替代方案。​

SUBSTITUTE 函数虽然看似简单，但却蕴含着巨大的能量，它能帮助我们高效处理文本数据，解决许多看似棘手的问题。在日常的工作和学习中，无论是处理财务报表、分析销售数据，还是整理学生成绩、管理客户信息，都可以尝试运用 SUBSTITUTE 函数，让数据处理变得更加轻松快捷。同时，也鼓励大家在实践中不断探索和总结，发现更多关于 SUBSTITUTE 函数的应用技巧和方法。​

如果你在使用 SUBSTITUTE 函数的过程中有任何独特的经验、有趣的案例，或者遇到了什么问题，都欢迎在评论区留言分享。让我们一起交流学习，共同提升 Excel 技能，更好地利用 Excel 这个强大的工具为我们的生活和工作服务 。期待看到大家的精彩分享！