做 Excel 的时候，你有没有遇到过这种情况？

明明两个人员名单看起来一模一样，结果：

• VLOOKUP 查不到数据
• XLOOKUP 返回 #N/A
• 两列数据比较，总是提示"不一致"

检查了半天，名字没错、编号没错，还是匹配失败。

后来发现，真正的问题根本不是数据，而是隐藏字符。

如：

张三

看起来是"张三"。

实际上可能是：

张三（后面多了一个空格）

或者：

张

三

（中间有一个换行符）

肉眼几乎看不出来，但 Excel 会认为它们完全不是同一个内容。

今天就教大家几种最实用的数据清洗方法。

一、去掉前后多余空格——TRIM

假设姓名这一列是：

其中有的数据前面、后面带着很多空格。

例如：

（前面带空格）张三

或者

张三（后面带空格）

使用公式：

=TRIM(A2)

结果：

TRIM 会自动删除：

 前面的空格

 后面的空格

 多个连续空格（保留一个）

这是数据匹配前最常用的一步。

二、去掉所有空格——SUBSTITUTE

有时候，空格不仅在两端，而是在文字中间。

例如：

希望变成：

公式：

=SUBSTITUTE(A1," ","")

执行结果：

这个方法会把所有普通空格全部删除。

三、去掉换行符——CLEAN

例如单元格里实际上是：

虽然显示两行，但你希望得到：

北京市朝阳区

公式：

=CLEAN(A2)

执行结果：

CLEAN 可以删除很多不可见控制字符，包括常见的换行符。

四、为什么用了 CLEAN 还是有换行？

这是很多人都会遇到的问题。

有些网页复制的数据，并不是 Excel 的换行，而是 CHAR(10)。

例如：

可以使用：

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

执行结果：

如果希望换行变成空格，而不是直接连在一起，可以写成：

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

结果：

这种方式阅读起来更舒服。

五、最彻底的数据清洗方法（推荐收藏）

实际工作中，经常会遇到：

• 前后有空格
• 中间有多个空格
• 有网页复制的换行
• 有不可见字符

这时候，建议直接使用组合公式：

=TRIM(CLEAN(SUBSTITUTE(A1,CHAR(10)," ")))

这个公式会完成四件事：

1. 删除换行符
2. 删除不可见字符
3. 去掉前后空格
4. 将多个连续空格整理成一个空格

对于大部分从系统、网页、聊天工具复制过来的数据，这个公式基本都能处理。

六、真实办公案例

某公司需要将两份员工名单进行匹配。

名单 A：

名单 B：

直接使用 XLOOKUP：

=XLOOKUP(A2,E:E,F:F)

结果：

#N/A

因为 Excel 认为：

张三

≠

张三␠

解决方法很简单。

先新增一列：

=TRIM(CLEAN(SUBSTITUTE(E2,CHAR(10)," ")))

把数据清洗后，再进行匹配。

结果立即恢复正常。

小技巧：

如果你经常做数据清洗，可以把下面这个公式保存起来，几乎适用于 90% 的文本清洗场景：

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

以后遇到"查不到、匹配不上、比较失败"时，不妨先试试它，很多问题都会迎刃而解。