文本清洗：TRIM、CLEAN、SUBSTITUTE、REPLACE

四个清洗函数的分工

数据清洗是 Excel 里最不出彩却最花时间的一环。系统导出的报表、网页复制的表格、同事手工填的台账，几乎都带着看不见的污渍：多余的空格、莫名其妙的换行、全角半角混用的括号、末尾多出来的单位。这些脏东西不会让单元格看起来有多异常，却会让你的查找匹配、条件统计、日期运算全部失灵。

负责清洗的四个核心函数是 TRIM、CLEAN、SUBSTITUTE、REPLACE。它们分属两类思路：TRIM 和 CLEAN 属于批量清理，不需要你指出具体是什么脏字符，函数自己按规则删；SUBSTITUTE 和 REPLACE 属于定点清理，你必须明确告诉它要换掉什么。TRIM 只管首尾与连续空格，CLEAN 只管不可打印字符，SUBSTITUTE 按内容替换，REPLACE 按位置替换。

TRIM 的语法是 =TRIM(文本)，作用是把文本首尾的空格全部删掉，并把中间连续出现的多个空格压缩成一个。注意它只认识半角空格，也就是字符代码为三十二的那个，其他任何看起来像空格的东西它都管不了。

CLEAN 的语法是 =CLEAN(文本)，作用是删除文本中所有不可打印的字符，也就是字符代码从一到三十一的那批控制字符。它主要针对从老式系统或主机终端导出的数据，现代网页复制过来的内容通常不含这些字符，但换行符比较特殊，它属于控制字符却不在 CLEAN 的清理范围内。

理解这张表的关键在于最后一列。四个函数没有一个是万能的，实际清洗时往往要嵌套两三层，比如先用 SUBSTITUTE 把不间断空格换成普通空格，再用 TRIM 去掉首尾。清洗公式一旦嵌套，建议先在辅助列里分段验证，确认每一层的输出都符合预期，再合并成一条公式。

还要提醒一件事：清洗一定会改变原始数据，所以在动手之前先备份一份原始列，或者把清洗结果写到新列而保留原列。很多事故都是因为就地清洗之后发现清错了东西，原始数据却已经找不回来。养成先复制一列再动手的习惯，能省掉很多追悔莫及的时间。

TRIM 与那些看不见的字符

TRIM 失效是最常让人抓狂的场景。你明明写了 =TRIM(A2)，结果单元格里看起来还是有个空格，用 LEN 一量长度也没变。这时候八成遇到的是不间断空格，字符代码一百六十，常见于网页复制和 PDF 里复制出来的文本。

处理办法是把不间断空格先换成普通空格，再交给 TRIM：

=TRIM(SUBSTITUTE(A2,CHAR(160)," "))

这个组合非常常用，建议直接记住。同理，如果数据来自中文排版系统，还可能带着全角空格（Unicode 的三千），TRIM 同样不认，需要再套一层：

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(12288)," "))

换行符是另一类顽固污渍。Excel 单元格里的换行通常用 CHAR(10) 表示，从某些系统导入的会用 CHAR(13) 或者两者成对出现。CLEAN 不管这些，必须用 SUBSTITUTE 逐个清掉：

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),""),CHAR(10),""))

如果你想把换行换成逗号，让多行内容压成一行，只要把最后的空字符串换成逗号即可：=SUBSTITUTE(A2,CHAR(10),"，")。这个写法在整理多行地址、合并多行备注时非常实用。

制表符 CHAR(9) 也经常出现，尤其是从文本文件导入的数据。清理逻辑完全一样，SUBSTITUTE(A2,CHAR(9)," ") 之后再 TRIM。如果你不想逐个猜，可以用 CODE 函数先看看那个可疑字符到底是什么：把光标放到可疑位置旁边，用 =CODE(MID(A2,5,1)) 取出第五个字符并看它的代码，比瞎猜快得多。

清洗完一定要验证。最可靠的验证方式是对比 LEN：清洗前长度如果是十一，清洗后应该变成十。建议在清洗列旁边加一列 =LEN(B2)，快速扫一遍有没有异常长的行，那些往往就是还有残留脏字符的地方。

SUBSTITUTE：按内容替换

SUBSTITUTE 的完整语法是 =SUBSTITUTE(文本, 旧文本, 新文本, [替换第几个])。前三个参数必填，第四个参数可选，省略时表示全部替换。

删除某个字符，就是把第三个参数写成空字符串：

=SUBSTITUTE(A2,"元","")

这个写法常用来去掉数值后面跟的单位。比如 A 列是 1,280元，想拿到纯数字，除了去掉“元”还要去掉千分位逗号，然后再转成数字：

=--SUBSTITUTE(SUBSTITUTE(A2,"元",""),",","")

前面两个负号的作用是把文本型数字转成真正的数字，这样才能参与求和与比较。如果原始数据里还有空格，记得在最外面再套一层 TRIM。

第四个参数的用法值得单独讲一讲。它用来指定只替换第几次出现的那一个，比如一串编码 A-01-02-A，你只想把第二个 A 换成 B：

=SUBSTITUTE(A2,"A","B",2)

如果不指定，两个 A 都会被换掉。这个特性在配合 FIND 做定位时非常有用，尤其是上一章讲过的取最后一个分隔符之后内容的写法，用的就是第四参数这个小技巧，可以回看 文本截取：LEFT、RIGHT、MID 和 LEN 里的相关段落。

统计某个字符出现几次，用的也是 SUBSTITUTE 加长度差的组合：

=LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))

SUBSTITUTE 区分大小写。要把 Excel 和 excel 一起替换，需要嵌套两次，分别指定大小写形式。REPLACE 则没有这个问题，因为它按位置操作，完全不看内容是什么。

需要提醒的是，SUBSTITUTE 不支持通配符，想用通配符做模糊替换是做不到的。遇到需要按模式替换的情况，通常的做法是先用 FIND 或 SEARCH 定位，再用 REPLACE 按位置替换，两者配合才能完成。

还有一个使用细节：SUBSTITUTE 的嵌套层数上限是六十四层，日常清洗通常套两到三层就够，但如果你发现自己在套第五层，说明思路可能错了，应该回头考虑用辅助列分步处理。把一条超长公式拆成三列，虽然多占了两列，但别人接手时看得懂，你自己三个月后回来改也省事。

REPLACE：按位置替换

REPLACE 的语法是 =REPLACE(原文本, 开始位置, 字符个数, 新文本)。它不看内容，只看位置，把从指定位置开始的若干个字符换成新内容。四个参数一个都不能省。

最常见的用途是给编码批量改段位。比如所有订单号都是 2023XXXX0001 这样的十二位，要把第五到第八位的年份段统一换成 2024：

=REPLACE(A2,5,4,"2024")

第二个参数是起始位置五，第三个参数是要替换掉的长度四，第四个参数是新内容。执行完之后 202300010001 就变成了 202400010001。

REPLACE 与 MID 是一对互补函数：MID 取出中间一段，REPLACE 把中间一段换掉。如果想在中间插入内容而不删除任何字符，把第三个参数写成零即可：

=REPLACE(A2,5,0,"-")

这个写法会在第五个字符之前插入一个短横线，原本的字符一个都不少，只是整体长度加一。这个技巧在给长串加分隔符时特别好用，比如把十一位手机号格式化成三位四段：

=REPLACE(REPLACE(A2,4,0,"-"),8,0,"-")

两层嵌套的逻辑是：先在第4位后插入一个横杠，再在第8位后插入。注意嵌套顺序要从原串长度较小的位置开始算，这里两处都不影响前面的字符，所以顺序随意；如果插入点靠后，就要考虑前一次插入改变了后续位置的问题，稳妥做法是从右往左依次插入，先处理靠后的位置，再处理靠前的位置。

REPLACE 的第三个参数如果大于剩余字符数，函数不会报错，而是把从起始位置到末尾的所有字符都替换掉。这个行为可以用来截断：写 =REPLACE(A2,6,999,"") 等价于保留前五个字符。不过更直观的写法还是直接用 LEFT(A2,5)，可读性更好，别人看公式时不用去数那个九百九十九是什么意思。

REPLACE 也常用来做脱敏。把手机号中间四位换成星号：

=REPLACE(A2,4,4,"****")

把身份证号出生年份段遮掉：

=REPLACE(A2,7,8,"********")

这种脱敏在对外发送报表时很常用，比手工改快得多，也不会漏。

真实场景与兼容提示

清洗一份从系统导出的客户表

假设导出的表里有三列脏数据：A 列客户名带首尾空格和换行，B 列金额带千分位逗号和“元”字，C 列电话后面跟着看不见的制表符。目标是生成三列干净数据。

客户名列：

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),""),CHAR(10),"")))

金额列：

=IFERROR(--SUBSTITUTE(SUBSTITUTE(B2,"元",""),",",""),0)

外套 IFERROR 是为了防止某些行是“待定”“暂无”这样的文字，转换失败时返回零，而不是让整列公式崩掉，导致下游的求和也一并出错。容错函数的更多用法可以在 信息函数：ISERROR、ISNUMBER、ISBLANK 里看到系统讲解。

电话列：

=TRIM(SUBSTITUTE(C2,CHAR(9),""))

三列清洗完之后，建议再各加一列长度校验：=LEN(D2)、=LEN(E2)、=LEN(F2)，扫一遍有没有异常值。这一步能拦住绝大多数漏网的脏字符，比事后返工划算得多。

批量清洗的三种做法

清洗整列数据有三条路，各有适用场景。第一条是辅助列写公式，好处是可追溯、可复用，下次拿到同样格式的数据直接拖一遍即可，坏处是多出一列，需要在清洗完成后用选择性粘贴转回数值再删列。这是最推荐的做法，尤其是数据要反复更新的时候。

第二条是用查找替换。按 Ctrl + H 打开替换对话框，在查找内容里输入要清理的字符，替换为留空，点全部替换。它的好处是原地修改、不留痕迹，适合一次性处理。麻烦在于换行符和不可打印字符没法直接敲进输入框，需要先在公式里用 CHAR(10) 生成，再复制那个结果粘进去。

第三条是分列。数据选项卡里的分列功能，选择固定宽度或分隔符号，能顺手把多余空格和固定结构的脏字符切掉。它适合结构规整、一次性的数据，不适合需要反复执行的操作。三种做法并不互斥，实际工作中往往是先用查找替换做粗清，再用辅助列公式做精清。

全角与半角的统一

中文数据里最烦人的问题之一是全角半角混用，比如括号有时是 （） 有时是 ()，数字有时是全角 １２３ 有时是半角 123。Excel 提供了两个专门函数：ASC 把全角字符转半角，WIDECHAR 把半角字符转全角。

统一成半角：

=ASC(A2)

统一成全角：

=WIDECHAR(A2)

做数据匹配前，建议先把两边的列都用 ASC 处理一遍，再统一 TRIM，能显著提高匹配成功率。

版本兼容性

TRIM、CLEAN、SUBSTITUTE、REPLACE 四个函数全版本通用，从 Excel 2003 到 Microsoft 365，从 WPS 到各类在线表格，行为完全一致，不需要做任何版本判断。ASC 和 WIDECHAR 在 Excel 与 WPS 里都存在，但对某些特殊符号的处理略有差异，建议转换后用 LEN 抽查一次。

TEXTJOIN 与 SUBSTITUTE 的组合在老版本里需要注意数组确认的问题，涉及数组运算时记得按三键。另外，WPS 里从网页粘贴数据时默认会带一层格式，清洗前先用“选择性粘贴为文本”落一次地，能省掉不少麻烦。清洗完之后如果需要把多段内容重新拼成一串，可以回到 文本拼接：连接符、CONCAT 和 TEXTJOIN 复习拼接的写法。

常见问题

为什么 TRIM 去不掉那个空格

因为 TRIM 只认字符代码为三十二的半角空格。网页和 PDF 里复制出来的文本常带不间断空格，代码是一百六十；中文排版系统里常用全角空格，Unicode 是三千。这两种 TRIM 都无能为力。修法是先用 SUBSTITUTE 把它们换成普通空格，再交给 TRIM：

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(160)," "),CHAR(12288)," "))

如果不确定那个字符到底是什么，用 =CODE(MID(A2,5,1)) 把它取出来看代码，把五换成可疑位置即可。这个方法比反复试错高效得多，建议作为排查脏字符的标准动作。

CLEAN 为什么清不掉单元格里的换行

CLEAN 的设计目标是删除字符代码一至三十一的控制字符，而 Excel 单元格内的换行符在多数环境下属于这一范围却不受它处理，这是历史遗留的行为差异。可靠的做法是用 SUBSTITUTE 直接点名清理，同时覆盖回车和换行两种：

=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(13),""),CHAR(10),""))

如果想保留分隔效果而不是彻底删掉，可以把第三个参数换成顿号或逗号，写成 =SUBSTITUTE(A2,CHAR(10),"，")，多行内容就被压成了一行，方便导出和打印。

怎么把带单位的文本金额转成可以求和的数字

思路是三步：删单位、删千分位、转数字。删单位和删千分位都靠 SUBSTITUTE，转数字靠乘以一或者双负号，最后套一层容错：

=IFERROR(--SUBSTITUTE(SUBSTITUTE(A2,"元",""),",",""),0)

如果单位不统一，有的写“元”有的写“万元”，就要先用 IF 判断再决定乘以多少：=IF(ISNUMBER(FIND("万",A2)),数值乘以一万,数值)。判断某个词是否存在，用 ISNUMBER 包一层 FIND 是标准写法，因为 FIND 找不到时返回错误值而不是假值。

SUBSTITUTE 和 REPLACE 到底该用哪个

判断标准很简单：你知道要换掉的内容是什么，但不知道它在第几位，用 SUBSTITUTE；你知道要换的东西在第几位从第几个字符开始，用 REPLACE。前者按内容匹配，后者按坐标操作。日常清洗里 SUBSTITUTE 用得多得多，因为脏字符的位置通常不固定；只有处理格式规整的编码、手机号、身份证号这类定长数据时，REPLACE 才更顺手。

清洗完之后怎么做批量替换回原表

清洗公式生成的是新的一列，不会改动原始数据。要把结果写回原位置，需要三步：选中清洗结果列并复制，右键在目标位置选择“选择性粘贴”，在弹出的对话框里选“数值”，确定之后再删掉临时列。注意一定要选数值而不是直接粘贴，否则粘过去的是公式，一旦删掉原始列就会全部变成引用错误。

为什么清洗后匹配还是对不上

清洗只解决了看得见和看不见的字符问题，对不上账还有另外三个常见原因。一是两边的数据类型不同，一边是文本一边是数字，需要统一用 -- 或 TEXT 转换。二是存在同音字或异体字差异，比如“帐号”和“账号”，这种只能人工核对。三是存在前后缀差异，比如一边带 NO. 前缀一边不带，需要先用 SUBSTITUTE 统一去掉。排查时可以用 =LEN(A2) 对比两边的长度，长度一致才是匹配成功的前提。