文本清洗三件套：TRIM、CLEAN、SUBSTITUTE

从网页、PDF、其他系统复制过来的文本，肉眼看着干净，实际上常混着前导空格、不间断空格（CHAR(160)）、换行符和不可见控制字符。这些字符会让 VLOOKUP 查不到、LEN 长度不对、数字无法求和。

一、三个函数的分工

函数	作用	典型场景
TRIM	去掉首尾普通空格，中间连续多空格压成一个	手工录入、复制粘贴
CLEAN	去掉 ASCII 0-31 的控制字符	网页/系统导出、换行符
SUBSTITUTE	按内容替换指定字符	不间断空格、全角字符

它们都不能互相替代。TRIM 去不掉换行，CLEAN 去不掉空格。

二、标准清洗公式

=TRIM(CLEAN(A2))

这是处理"来源不明脏文本"的默认第一层。但遇到不间断空格就失效了，因为 CHAR(160) 不是普通空格，TRIM 不认它。

完整版：

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

先把不间断空格换成普通空格，再 TRIM 收尾。

三、怎么发现脏字符

先用 LEN 对比裸眼长度：

=LEN(A2)

如果 LEN 结果比看起来多 1-2 位，就是有不可见字符。再用 CODE 定位是哪种：

=CODE(LEFT(A2,1))         ' 首字符编码，32 是空格，160 是不间断空格
=CODE(RIGHT(A2,1))        ' 尾字符编码，10 是换行

也可以用 UNICODE 查非 ASCII 字符。

四、换行符处理

单元格里的换行来自两种情况：

• 用 Alt+Enter 手工换的行，是 CHAR(10)；
• 从网页复制来的，可能是 CHAR(13)（回车）或两者的组合 CHAR(13)&CHAR(10)。

想全部压平成一行的空格：

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

想让每个换行变成统一的分隔符，比如逗号：

=SUBSTITUTE(A2,CHAR(10),"、")

五、全角与半角

中文输入法下敲的空格是全角（CHAR(12288)），占两位宽度，肉眼和半角很像：

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

全角数字、全角字母也常见于从中文文档复制的数据。批量转半角可以借助 ASC：

=ASC(A2)

ASC 把全角字符转半角，处理中文文档里的数字特别有用。

六、多行文本一次性清洗

对整列做清洗，两个效率做法：

1. 辅助列写 =TRIM(CLEAN(A2))，下拉，然后复制这一列 → 选择性粘贴数值回原列，再删辅助列；
2. 数据量小，直接改单元格，改完按 F2 → Enter 触发重算不会自动清洗，还是得走公式。

第 1 种是标准做法。全选辅助列 → 复制 → 右键原列 → 选择性粘贴 → 值，原数据就被覆盖成干净版本。

七、常见坑

• TRIM 后长度只少了 1：说明中间有连续空格，TRIM 会压缩中间空格，不止首尾。
• CLEAN 后仍有奇怪字符：CLEAN 只管 0-31，中文标点、全角空格、不间断空格都得用 SUBSTITUTE。
• 清洗后查重还是重复：可能还有一些不可见字符没清掉，用 LEN 再核一次。
• 数字清洗后仍不能求和：值本身存成了文本，清洗后还要 -- 或 VALUE 转数值。
• 批量粘贴时把公式也粘过去了：选择性粘贴必须选"值"。

八、速查

需求	公式
基础清洗	=TRIM(CLEAN(A2))
含不间断空格	=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
去换行	=TRIM(SUBSTITUTE(SUBSTITUTE(A2,CHAR(10)," "),CHAR(13)," "))
查字符编码	=CODE(LEFT(A2,1))
全角转半角	=ASC(A2)

清洗这件事一次做对，比后面每一处公式都套一层 TRIM 划算得多。