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

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

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

情况分析

原始Excel表显示如下：

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

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

图2 - 透视表显示错误

原因分析

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

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

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

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

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

什么是非打印字符？

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

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

1. 用Excel自带的CLEAN函数

函数语法：CLEAN(text)参数说明：text，表示要删除非打印字符的文本。功能说明：删除文本中所有不能打印的字符。 对从其他应用程序导入的文本使用 CLEAN，将删除其中含有的当前操作系统无法打印的字符。 例如，可以使用 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---

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