Excel有隐藏字符看不见，其实那叫非打印字符，如何清洗

上月收到一个财务同事的Excel求助：

同事J：“收到的表格里有隐藏的空格和双引号" "，在Excel里不显示，数据透视表求和也不正确。目前我们只能通过复制到text文档。双引号“”可以通过替换功能全部清除，空格手动一行行删，再粘回excel。很笨很低效率。这个问题已经困扰我们几年了。大神能不能帮我们看看。”

情况分析

原始Excel表显示如下：

图1 - 原表格看不见隐藏字符

数据透视表报错“值不可用”错误，显示如下：

图2 - 透视表显示错误

原因分析

复制粘贴到文本文档里显示如下，发现付款人名称字段里面包含了空格和双引号：

图3 - 复制到文本文档查看

这才是真正的完整数据，而在上图Excel里空格和双引号被隐藏了。这种情况实际上是“非打印字符”惹的祸。

再到站长之家里用Unicode编码转换工具验证一下这些字符，直接复制单元格数据到左侧的框里，然后点“ASCII转Unicode”,你可以看到这些字符对应的ASCII码。你就知道原来隐藏的其实是制表Tab键和双引号。

图4 - 用站长工具准确确认

什么是非打印字符？

非打印字符指在计算机中有一些字符是确确实实存在,但是它们不能够显示或者打印出来。以ASCII码表为例,ASCII码值在0-31的为控制字符,无法显示和打印，比如回车键。

知道了根本原因之后，我们解决起来就可以对症下药，解决的办法有三种：

1. 用Excel自带的CLEAN函数

图5 - 用CLEAN()函数清洗

处理之后，再复制到文本文档里，你就发现空格和双引号都消失了。

图6 - 用CLEAN()函数清洗效果确认

2. 说到清洗数据，我们自然也要想到Excel里强大的数据清洗插件POWER Query，用Power Query的“修整”和“清除”，这两种方法也都可以实现。为了方便显示，我们用添加列选项卡里的"格式"来做个对比。

• 选中数据列，分别点格式里的修整和清除。

图7 - 用POWER Query清洗

如下图显示：

图8 - 用POWER Query的修整和清除功能清洗

• 然后点“关闭并上载”

图9 - 清洗后关闭并加载

加载到清洗后的表格如下：

图10 - 用POWER QUERY清洗后的数据

• 然后我们再复制到文本文档里确认，成功清除。

图11 - 用POWER QUERY清洗后效果确认

作业成功交付。

同事J : “大神，请接受我的膜拜！三种方式都可行。感谢解惑，学习了。”

解决了困扰他们几年的问题，从此在同事J的部门树立江湖地位，哈哈。

---End---

此类问题多见于由系统导出的数据。若有别的好的方法，也请各位朋友一起交流一下。