SUBSTITUTE 函数：语法、示例与错误排查
更新于 2026-10-13 · 免费 · 兼容 Excel 与 Google Sheets
语法
=SUBSTITUTE(text, old_text, new_text, [instance_num])快速答案
SUBSTITUTE 把一段文本换成另一段，出现的每一处都换：=SUBSTITUTE(A2, "-", " ") 把「555-01-2345」变成「555 01 2345」。它区分大小写，不加 instance_num 就全量替换，而 instance_num 为 0 会返回 #VALUE!。
语法
=SUBSTITUTE(text, old_text, new_text, [instance_num])| 参数 | 含义 |
|---|---|
| text | 要搜索的单元格或字面量。必填。 |
| old_text | 要替换的确切文本。匹配区分大小写、按字面进行——不支持通配符。必填。 |
| new_text | 替换成的内容。空字符串 "" 会把匹配直接删掉。必填。 |
| [instance_num] | 替换第几次出现：3 只改第三处。省略则每一处都换。必须为 1 或更大的数。 |
视区域设置不同，参数可能用分号而不是逗号分隔：=SUBSTITUTE(A2; "-"; " ")。在用逗号作小数分隔符的国家/地区是标准行为。
两个行为总让人意外：匹配区分大小写（「ID」和「id」是两个不同的目标），而且不加 instance_num 时替换命中每一处——没有「只换第一个」的开关。
基本示例
=SUBSTITUTE(A2, "-", " ")横杠换空格：用连字符写的电话号码、零件编号、证件号码一步变可读。每一道横杠都会变——公式贯穿整个单元格重复这次交换。
=SUBSTITUTE(SUBSTITUTE(A2, "(", ""), ")", "")删除字符：new_text 写 "" 会让每个匹配无痕消失。嵌套两层 SUBSTITUTE，一条公式把「(555) 0102」的两边括号全剥掉——想删几种字符就套几层。
=SUBSTITUTE(A2, "/", "-", 2)instance_num 参数锁定单独一次出现：只有第二条斜杠变成横杠，第一条和第三条原地不动。出现次数从文本开头从左往右数。
=LEN(A2)-LEN(SUBSTITUTE(A2, " ", ""))+1经典字数统计：把所有空格删掉，比较两个长度得到空格个数，+1 就是单词数。不用 VBA、不用数据分析——一条公式。
SUBSTITUTE 的结果是文本——哪怕看着像数字。清洗「1 234」得到的「1234」不能求和、求平均、画图。包一层：=VALUE(SUBSTITUTE(A2, " ", "")) 把清洗后的文本变回真数字。
常见错误与修复方法
| 症状 | 最常见原因 | 修复 |
|---|---|---|
| 什么都没被替换 | 大小写不匹配——「ID」匹配不上「id」——或看不见的尾随空格让「abc 」和「abc」成了两个东西。 | 大小写完全对齐、先用 TRIM 清洗源数据，或改用「查找和替换」对话框——大小写匹配在那里是个勾选项，不是硬规则。 |
| #VALUE! | instance_num 是 0 或负数。 | 用 1 或更大的数——或干脆省略该参数，替换全部出现。 |
| 改错了位置 | 出现次数从左往右数，instance 2 命中的是你想保留的那处。 | 先用 LEN 技巧数一数出现几次，或用上下文让目标唯一：=SUBSTITUTE(A2, "/2024/", "/2025/") 只命中这个模式里的斜杠。 |
| 换的比预期多 | 短的 old_text 会命中长单词的内部：替换「1」会连「12」「21」一起中招。 | 把周围的字符一起写进 old_text，或用 instance_num 锁定出现位置。 |
| 数字不能计算了 | 结果是文本，哪怕看着是数字。 | 整条包进 VALUE，或计算继续用原始单元格。 |
还有一种无声的空操作：当 instance_num 超过实际出现次数时，SUBSTITUTE 原样返回原文——不报错、不提示。如果公式「什么都没做」，先数一数匹配有几处，再怀疑拼写。
SUBSTITUTE 与 REPLACE 对比
| SUBSTITUTE | REPLACE | |
|---|---|---|
| 定位方式 | 按文本本身，在单元格的任何位置 | 按固定位置：起始位置 + 字符个数 |
| 适用场景 | 知道要换什么，不知道它在哪 | 知道确切的位置和长度 |
| 作用范围 | 每一处出现——或用 instance_num 锁定一处 | 每次调用只换一个固定块 |
| 示例 | =SUBSTITUTE(A2, "-", " ") | =REPLACE(A2, 4, 2, "XX") |
REPLACE 按固定位置换字符——布局死板时是对的工具，比如「永远覆盖第 4–5 个字符」。一旦目标文本可能出现在单元格的任何地方，就交给 SUBSTITUTE：它找的是文本，不是位置。
SUBSTITUTE 与查找替换对比
| SUBSTITUTE | 查找和替换（Ctrl+H） | |
|---|---|---|
| 存在形式 | 一条公式——结果在自己的单元格里，源单元格不动 | 一个对话框——直接改单元格，覆盖原值 |
| 是否重算 | 是——源数据一变，替换结果跟着变 | 否——一次性；新数据要重新跑 |
| 覆盖范围 | 向下填充让一条公式变成千条 | 一次跑遍整个工作簿 |
| 大小写处理 | 永远区分大小写 | 默认不区分，有「区分大小写」勾选项 |
实用分工：一次性清理表里已有的东西用「查找和替换」；清理必须扛得住新数据——导入、刷新、成片粘贴——就用 SUBSTITUTE。很多表的结局是一列原始导入数据加一列 SUBSTITUTE 清洗列。
Google Sheets 中的 SUBSTITUTE
名字、四个参数、区分大小写的字面匹配全都一样——上面的公式可以直接粘贴过去。Sheets 还把同样的思路做成了强力工具：REGEXREPLACE 接受正则表达式，一条公式能同时收拾一大堆乱格式。
- 逗号作小数分隔符的地区改用分号：=SUBSTITUTE(A2; "-"; " ")。
- Sheets 的「查找和替换」对话框（Ctrl+H）有「使用正则表达式搜索」选项，Excel 没有。
- 删除字符时 new_text 写 ""，行为与 Excel 完全一致——不留痕迹。
限制与进阶技巧
- 每次调用只认一个 old_text：清洗几种不同字符就得嵌套——每层多剥掉一类字符。
- 模式有重叠时顺序很重要：先换「::」再换「:」，否则轮到单冒号规则时双冒号已经没了。
- 不支持通配符——old_text 按字面匹配，「*」和「?」找的就是星号和问号本身。模式匹配的火力在「查找和替换」里，Sheets 里在 REGEXREPLACE。
- 换行符是 CHAR(10)：=SUBSTITUTE(A2, CHAR(10), " ") 把带换行的单元格压成一行——需要的话再把结果转成真数字。
- 可复用的清洗流水线：SUBSTITUTE 去噪、TRIM 去残、VALUE 转数——三列存活于每一次重新导入。
把 SUBSTITUTE 当作导入表的免疫系统：原始数据落地就是脏的，substitute 列在落地时清洗，下游每条公式都吃干净版本——再也没有人手工编辑原始列。
相关工具
相关函数
常见问题
SUBSTITUTE 在 Excel 中有什么用？
它把一段文本换成另一段，单元格内每一处都换：=SUBSTITUTE(A2, "-", " ") 把每道横杠换成空格。加上可选的 instance_num 参数，也可以只改指定的某一次出现。
SUBSTITUTE 区分大小写吗？
区分——old_text 必须大小写完全一致，也没有忽略大小写的开关。一次性的不区分大小写清理用「查找和替换」对话框，那里是「区分大小写」勾选项，不是硬规则。
怎么只替换第二次出现？
把 2 作为第四个参数：=SUBSTITUTE(A2, "/", "-", 2)。出现次数从左往右数，其余匹配原样不动。1 起的任意数字都行，0 或负数返回 #VALUE!。
怎么删掉单元格里所有空格？
替换成空：=SUBSTITUTE(A2, " ", "")。空的 new_text 会删掉每个空格。结果像数字的话，之后包一层 VALUE，让清洗后的文本重新参与计算。
SUBSTITUTE 和 REPLACE 有什么区别？
SUBSTITUTE 全场搜捕文本；REPLACE 覆盖固定的位置和长度（=REPLACE(A2, 4, 2, "XX")）。知道要改什么用 SUBSTITUTE，知道确切在哪用 REPLACE。
为什么 SUBSTITUTE 返回 #VALUE!？
第四个参数 instance_num 是 0 或负数。它必须是 1 或更大的数，或者整个省略以替换所有出现。超过实际出现次数不算错误：文本原样返回。
SUBSTITUTE 在 Google Sheets 里能用吗？
能——名字、参数、区分大小写的字面匹配全都一样。Sheets 还提供基于正则的 REGEXREPLACE，以及支持正则的查找替换对话框。
准备好处理你自己的文件了吗？
打开Excel 批量查找替换 — 免费