好文: 不可见字符

小伙伴们好啊，我是流浪铁匠，今天为大家介绍的是excel数据整理时一类最常见的坑——不可见字符。

从unichar函数对应的uincode字符集结果来说，excel大约有111万+个字符，其中有不少字符的性质千奇百怪，由于unichar/unicode函数出现得晚(2013版本新增函数)，因此现在还在用2010或以前版本的小伙伴们，如果经常面临网页或软件导出的数据，那么总会被一些看不到的字符影响统计和计算。

接下来和大家一一道来excel里各种常见的不可见的字符与对应解决方法。

0，文本识别符和空文本

字符串最前面的一个半角单引号。

严格来说，这个字符不属于字符串内的字符，而是excel的特殊格式设定，在字符串前加上这个半角单引号会使整个字符串强制识别为文本，无视之前单元格格式的设定。

这个半角单引号的特点是只在编辑栏显示但单元格内不显示。

这个字符的存在不影响字符串匹配(除了数据类型差异)但是总有强迫症患者问怎么清除这个字符，已经解释了这字符属于特殊格式设定。

因此不能使用字符的处理方式，需要使用格式的方式:

找个空单元格把格式用格式刷刷过来，或者使用开始菜单下右侧编辑部分的清除格式功能清除这个字符。

另外一类不属于不可见字符但经常混为一谈的东西为空文本，即假空。

假空是和真空(单元格)相对的，共同点是使用len函数对这个单元格计算字符数时结果都是0（0字符）。

假空多由于公式结果或者软件导出造成，虽然len函数的字符计算结果也是0，但无法被定位-空值的定位操作定位到相应单元格。

假空在公式里以""表示，因此本质还是文本，虽然无法用len函数检测出来，但是可以用istext或者isblank等函数检查出来与真空的差异。

注意countblank函数不区分真空与假空。

技巧操作里可以使用分列直接点完成方式把假空处理为真空。

或者在查找时查找框什么都不输的方式对查找到的单元格ctrl+A全选来定位真空+假空单元格进行定位，用于后续处理(选中后右键清除内容即可把全部假空单元格处理为真空)。

上面这2种不属于字符但经常被混为一谈(因为这2种不能被len函数检查出来)所以先介绍下。

然后我们该聊聊excel里各种影响常规匹配与核对/计算的各种不可见字符了。

1，空格

这是最容易发现的不可见字符吧。

肉眼只能看到2个字符但len结果为3，所以存在不可见字符。

空格是占字符宽度的，因此可以在编辑栏内直接选中抹黑发现。

code函数对空格返回的字符编码结果为32，可以用这个函数快速定性，清除方式可以直接替换为空(什么都不输)。

函数处理如果为两侧空格可以用trim函数快速清除(这函数不能清除中间作为间隔符的空格，中间的要用substititute+char(32)清除。

(记住trim函数只能清除2个字符:空格与其全角形式(char(32)与char(41377)))。

2，非打印字符

这几个字符里大家比较熟悉的是char(10)换行符。属于常见的非打印字符，但对新手来说这些字符比空格相对隐蔽，因为像char(9)这字符在单元格内是不占宽度的，无法直接使用抹黑方式检查。

江湖上有一招叫照妖镜就是针对这类性质，即把字符串复制粘贴在记事本或者word将字符现行，再复制粘贴进行替换清除。

粘在记事本里能发现存在多余字符。

非打印字符可以使用分列功能清除，函数里通常使用clean函数直接清除这部分字符。

然而clean的清除能力是有限的，只能清除code结果为char(1-31，128)合计32个字符，。

如果用unicode函数则结果为unichar(1-31，128-159[这31个在中文语言下code的结果是0])范围合计63个字符。

综上所述，其实trim和clean的清除能力其实很有限(不可否认能清除的都是不可见字符类型里的常见字符)。

还有很多字符是这2个函数清除不了的，最典型的是unichar(160)，这个字符在微软的trim函数帮助内有提及，不间断空格字符，常见于网页。

在单元格中的性质和空格很相似但无法用trim清除，这个字符的快速清除方式为直接复制后替换为空(什么都不输)清除。

这个字符对新手来说最大的误区在于用code函数获取的编码值为63，但不能使用substitute+char(63)清除。

而code结果为63的不可见字符，是新手最容易出现的误区，也是这篇文章的重点内容。

3，code结果为63的不可见字符

由于早期版本函数code与char的不完全逆运算以及使用的字符集数量有限，。code函数会把所有不识别的字符全部默认为 ? ，因此code结果都是63。

而遍历下来code结果为63的字符大约有108万+个(你没看错数字，code结果不为63的字符只有3万+)。因此使用clean与trim都无法清除的不可见字符，通常code结果都是63。

如果该字符类似空格在单元格内有宽度，可以直接复制替换清除。

如果类似char(9)的性质，无法抹黑发现，通常准确的清除方式为使用unicode函数获取对应编码后使用unichar+substitute函数清除。

关于这部分网上也有很多代码，但测试下都没有能100%清除干净的，和代码本身无关，重点是要有全部的不可见字符编码表，但这个会涉及到很多环境因素影响，例如字体等都会影响某些字符的可见性)。

因此如果在不可见字符规则不规范的情况下处理这类字符的最准确方式为确认对应unicode编码后使用函数/vba/pq等替换/移除来清除。

此类不可见字符里最危险的字符为unichar(8204)，unichar(8205)等等。

因为这批字符有几个特殊性质:

1，code结果为63。

2，单元格内无宽度。

3，在记事本与word里也不会现行。

4，trim与clean函数不能清除。

5，这些字符虽然占字符数但在用等号判断时不影响结果(图里紫色的公式)。

这类字符最大的特性在于第5条，excel里只有3709个字符有此性质(365版本下有老师测试过结果为2000+)。

这个判断性质对于新手极其容易造成核对时出现错误。

4，新版本，新工具

4.1 pq的不可见字符清除

上面介绍的是各种不可见字符与其清除方式。大家可以看到直到2013版本出现unichar与unicode函数后这种问题才能有效准确解决，。

然而除了这2个新函数，高版本的新工具在一定程度上也能有效处理这类问题。Power query 内的转换-文本列-格式内有2个功能(修整，清除)(对应Text.Trim和Text.Clean2个m函数)。

其中清除(Text.Clean)能力和工作表函数clean能力一致。

但Text.Trim能清除的字符比工作表函数trim多太多了(注意是两侧的)。

这说明微软一直知道某些不可见字符处理的常见坑但是在工作表函数范畴一直没解决，在pq里才有一定程度得处理(注意还是不能清除上述最特殊的unichar(8204)等字符)。

可以使用Text.Remove+Character.FromNumber 清除。

4.2，应用商店

2016自带的应用商店里有不少实用小工具，如XLTools.net Data Cleaning，专司清除和转换字符的数据类型。

测试下能清除unichar(160)等不可见字符，当然还是不是万能的，但是操作不难，适合新手使用，重点是上述最危险的unichar(8204)这种字符也能被直接清除。

调用方式为2016的插入-加载项的应用商店里搜索或在分类中找到该工具添加，在我的加载项内调用。

调用后excel界面右侧出现对应菜单。

Step 1选取处理区域，step2选择要清除的不可见字符类型(两端空格，不相干的干扰字符，换行符，非打印字符等等)。

虽然极少数偏僻字符也不能处理，但已经能把绝大多数干扰字符清洗干净了(测试下能清除8000+个字符)。

这就是高版本带来的好处，低版本难以实现的问题我们经常可以借助高版本的新工具快速解决了。

今天的内容对于部分小伙伴来说可能有点生疏，但是如果你经常处理从网上下载或是从系统导出的数据，这些内容一定会帮到你。

图文作者：流浪铁匠