一、函数概述

SUBSTITUTE 是 Excel 中用于替换文本字符串中指定内容的函数。它通过匹配文本内容（而非位置）进行替换，非常适合数据清洗、格式统一和内容修正 。

核心特性：

• ✅ 内容匹配：按文本内容查找替换，而非字符位置
• ✅ 全部或指定替换：默认替换所有匹配项，也可只替换第 N 次出现
• ✅ 区分大小写：精确匹配大小写（与 REPLACE 不同）
• ✅ 不支持通配符：只能精确匹配，不能用 * 或 ?
• ✅ 嵌套使用：可嵌套实现多重替换

---

二、语法结构

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

参数	必需	说明
text	是	要处理的原始文本或单元格引用
old_text	是	要替换的旧文本（被查找的内容）
new_text	是	用于替换的新文本
instance_num	否	指定替换第几次出现的旧文本（省略则替换所有）

---

三、参数详解

参数	关键说明
text	可以是直接文本（加引号）如 "ABC"，或单元格引用如 A1
old_text	必须完全匹配，区分大小写。如 "apple" 不会匹配 "Apple"
new_text	可为空字符串 ""，实现删除效果
instance_num	第 N 次出现指的是旧文本出现的次序，不是字符位置

---

四、实用示例

示例 1：基础替换（替换所有匹配项）

场景：将所有”2023″替换为”2024″

=SUBSTITUTE(A1, "2023", "2024")

• A1 内容："2023年报表2023版"
• 结果："2024年报表2024版"（所有”2023″都被替换）

---

示例 2：只替换第 N 次出现

场景：文本中有多个相同内容，只替换特定位置

=SUBSTITUTE("香蕉、苹果、香蕉、葡萄、香蕉", "香蕉", "橘子", 2)

• 只替换第 2 次出现的”香蕉”
• 结果："香蕉、苹果、橘子、葡萄、香蕉"

只替换第一次：

=SUBSTITUTE(A1, "苹果", "橙子", 1)

• 结果："橙子,苹果,香蕉"（只有第一个被替换）

---

示例 3：删除特定字符（替换为空）

场景：删除所有连字符”-“

=SUBSTITUTE("2024-01-01", "-", "")

• 结果："20240101"（日期格式转换）

删除星号：

=SUBSTITUTE(B5:B16, "*", "")+0

• 删除所有星号，+0 将文本转为数字

---

示例 4：替换换行符（软回车）

场景：将单元格内的换行符替换为逗号+空格

=SUBSTITUTE(B5, CHAR(10), ", ")

• CHAR(10) 表示换行符（ASCII 10）
• 常用于清理从网页或邮件复制的数据

---

示例 5：嵌套 SUBSTITUTE（多重替换）

场景：同时替换多个不同的字符（如左右括号）

=SUBSTITUTE(SUBSTITUTE(B5, "(", ""), ")", "")

• 内层：先替换左括号为空
• 外层：再替换右括号为空
• 结果：删除所有括号

标准化多个状态术语：

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "Dispatched", "Shipped"), "Sent", "Shipped"), "shipped", "Shipped")

• 将”Dispatched”、”Sent”、”shipped”统一替换为”Shipped”

---

示例 6：数据清洗（修正拼写错误）

场景：自动修正常见的拼写错误

=SUBSTITUTE(A2, "recieve", "receive")

• 将所有”recieve”（错误）替换为”receive”（正确）

配合 PROPER 函数首字母大写：

=PROPER(SUBSTITUTE(A3, "recieve", "receive"))

• 替换后再将首字母转为大写

---

示例 7：日期格式转换

场景：将点分隔的日期转为标准日期格式

=VALUE(SUBSTITUTE(A2, ".", "/"))

• 将 "23.11.25" 转为 "23/11/25"
• VALUE 函数将文本转为真正的日期值

---

示例 8：动态数组批量处理（Excel 365）

场景：一次性处理整列数据

=SUBSTITUTE(B5:B16, "*", "")+0

• 直接引用区域 B5:B16
• 结果自动溢出到相邻单元格

---

五、SUBSTITUTE vs REPLACE 对比

特性	SUBSTITUTE	REPLACE
查找方式	按内容匹配	按位置和长度
语法	=SUBSTITUTE(文本, 旧文本, 新文本, [第N次])	=REPLACE(文本, 开始位置, 字符数, 新文本)
位置要求	不需要知道位置	必须知道开始位置和长度
大小写	区分大小写	不区分大小写
适用场景	替换已知内容的文本	替换特定位置的字符

示例对比：

=SUBSTITUTE("Excel 2021", "2021", "2024")     ← 知道内容"2021"
=REPLACE("Excel 2021", 7, 4, "2024")          ← 知道位置第7位，长度4

---

六、注意事项

注意点	说明
区分大小写	"Apple" 不会匹配 "apple"，需确保大小写一致
完全匹配	不会部分匹配，如 "苹果" 不会替换 "苹果汁" 中的前半部分
无通配符	不支持 * 或 ?，如需模式匹配请用 REGEXREPLACE（Excel 365）
返回文本	结果始终为文本，如需计算需用 VALUE 转换
版本兼容	所有 Excel 版本都支持（Excel 2007+）

---

七、常见错误与解决

错误	原因	解决
文本未替换	大小写不匹配	检查 old_text 与实际文本的大小写
只替换部分	使用了 instance_num 参数	删除第4参数以替换所有
#VALUE! 错误	instance_num 为负数或0	确保 instance_num ≥ 1

---

八、推荐组合技巧

组合函数	用途	示例
PROPER/UPPER/LOWER	替换后调整大小写	=PROPER(SUBSTITUTE(A1,"old","new"))
VALUE	文本转数字	=VALUE(SUBSTITUTE(A1,"-",""))
TRIM	去除多余空格	=TRIM(SUBSTITUTE(A1," "," "))
CHAR(10)	处理换行符	=SUBSTITUTE(A1,CHAR(10),", ")
LEN	计算替换前后长度变化	=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))