Excel数据清洗之六 去除不可见字符
造成分类不统一的原因还有一种，叫做“不可见字符”。
先看下面的例子。假设我们数据如下：
表中所有数据都是同一个ID，如果我们用数据透视表汇总它们的数量，我们期望会得到一个这样的结果，
但是，实际上，我们会得到这么一个结果：
显然，Excel并不认为这三个ID是相同的。
分析
我们来看一下源数据的三个ID。先后用函数LEN来计算一下分别的长度：
三个看上去“一模一样”的ID，长度却各不相同。
首先看第三行的那个ID（B3），很明显，有9个字符，所有公式LEN(B3)的结果是9，说明这是一个正常的ID（没有多余的字符之类的）。
而第四行的ID（B4），却有12个字符。多出来的是什么呢？一般我们总是会怀疑这些多出来的字符是空格。我们可以通过下面的方法来确认一下：
选中B4，鼠标点击编辑栏，在编辑栏中按Ctrl+Shift+⬇（下箭头），
可以看到，后面多出来的部分（箭头所指）就是空格。
空格很简单，我们在上一讲已经讲过，用替换或者TRIM函数就可以处理了。
再来看第五行的ID（B5），用同样的方法，看一看编辑栏中能否发现空格，
可以看到，这次编辑栏中所选中的文本就是ID的那9个字符，并没有多余的字符，当然也就说明没有空格。
这种在计算长度时占据了1个字符，但是在编辑栏中去不能发现的字符，就叫做“不可见字符”，又叫“不可打印字符”。
不可见字符的来源
产生不可见字符的来源主要是不同系统的数据源转换时带来的。文本文件中的数据，网页上的数据，或者数据库系统的数据转换到Excel中时，往往很可能就会产生这种不可见字符
如何清除不可见字符
一般来说，清除不可见字符要用函数：CLEAN
使用很简单：
在这个简单的例子中，我们可以使用TRIM和CLEAN这两个函数，将多余的空格和不可见字符一起处理掉，
想用CLEAN清除不可见字符，然后再用TRIM清除多余空格，最后得到的就是干净的数据了。讲这一列（处理列，D3:D5）数据复制数值到B3:B5，这一步数据清洗工作就完成了。
顺便说一下，上一讲我们讲到多余的回车（换行）时，讲到用“替换”清除多余的回车。实际上这个回车也属于不可见字符，可以用CLEAN清除掉。
一个特殊的“空格”
有一种特殊的空格，可以在编辑框中选中它，
但是这个空格不能用TRIM清除掉，也不能用CLEAN清除掉。
如何制造这个特殊的“空格”
在单元格中输入公式：=“CA1001101”&UNICHAR(160),然后将这个单元格复制，粘贴数值到其他单元格，即可得到这个结果。
这个空格是UNICODE编码为160的字符，名字叫做“不间断空格字符”，简称nbsp，在网页中经常使用。
清除这个字符的最简单方法就是在编辑栏中选择这个字符，然后在替换对话框中，将它替换为空，
也可以使用公式：
=SUBSTITUTE(B2,UNICHAR(160),"")其中函数SUBSTITUTE的作用是在一个文本中将一个字符串替换为新的字符串。
这个公式的意思就是将B2中的文本中的那个“空格”（UNICHAR(160))替换为空。
总结以及其他
看上去，对于不可见字符，CLEAN确实是我们能够依靠的“关键”函数了。确实是这样的。很多（在我们经常遇到的不可见字符的场景中，可以说是大多数了）不可见字符都可以用CLEAN清除。但是仍然有不少的场景中，我们会遇到那些用CLEAN清除不掉的情况。而那些用CLEAN清除不掉的字符，实际上是非常多的（尽管出现的概率不大）。
如果你遇到了这种用CLEAN清除不掉的情况，可以通过找到这个字符的UNICODE编码，然后通过SUBSTITUTE来替换掉。幸运的是，这些特殊字符往往在一个字符串的开头（或者结尾），
例如，假设B2单元格有一个ID，我们发现它含有不可见字符，用LEN计算长度，也确认了这一点，
我们可以首先通过下面的公式取出第一个字符，看看这个特殊字符是否在开头，
=LEFT(B2)很显然，如果这个公式返回“C”，就说明开头没有特殊字符，那么这个特殊字符就在后面。如果这个公式返回一个看上去什么也没有的“空单元格”，说明第一个字符就是我们要找的特殊字符。
然后可以使用公式：
=UNICODE(LEFT(B2))会得到一个类似于下面的数字：
然后就可以使用公式了
=SUBSTITUTE(B2,UNICHAR(8204),"")当然，如果你的数据比较复杂，这个任务可能很艰巨，你可以关注下面的公众号：ExcelEasy寻找更多方法和技巧，或者使用我们的数据清洗服务，让我们来帮助你完成这个工作。