excel中不可见字符，excel里最隐蔽的坑

Excel函数 2023-01-08

今天为大家介绍的是excel数据整理时一类最常见的坑——不可见字符。从unichar函数对应的uincode字符集结果来说，excel大约有111万+个字符，其中有不少字符的性质千奇百怪，由于unichar/unicode函数出现得晚(2013版本新增函数)，因此现在还在用2010或以前版本的小伙伴们，如果经常需要网页或软件导出的数据，就可能会被一些看不到的字符影响统计和计算。接下来就和大家一起说说excel里各种常见的不可见的字符与对应解决方法。

1、文本识别符和空文本

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

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

这个半角单引号的特点是只在编辑栏中显示，单元格内不显示。这个字符的存在不影响字符串匹配(除了数据类型差异)但是总有强迫症患者问怎么清除这个字符，已经解释了这字符属于特殊格式设定。

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

找个空单元格把格式用格式刷刷过来，或者使用开始菜单下右侧编辑部分的清除格式功能清除这个字符。（注意，设置完成后，要双击单元格，单元格才能变成常规模式。）

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

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

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

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

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

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

当然，也可以使用分列，进入分列页面后直接点完成，把假空处理为真空。

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

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

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

2、空格

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

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

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

code函数对空格返回的字符编码结果为32，可以用这个函数快速定性，清除方式可以直接替换为空(什么都不输)。函数处理如果为两侧空格可以用trim函数快速清除(这函数不能清除中间作为间隔符的空格，中间的要用substititute+char(32)清除。(记住trim函数只能清除两个字符:空格与其全角形式(char(32)与char(41377)))。

3、非打印字符

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

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

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

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

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

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

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

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

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

4、code结果为63的不可见字符

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

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

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

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

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

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

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

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑

1，code结果为63。

2，单元格内无宽度。

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

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

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

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

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

5、新版本，新工具

5.1 pq的不可见字符清除

上面介绍的是各种不可见字符与其清除方式。大家可以发现直到2013版本出现unichar与unicode函数后这种问题才能有效准确解决。然而除了这2个新函数，高版本的新工具在一定程度上也能有效处理这类问题。Power query 内的转换-文本列-格式内有2个功能(修整，清除)(对应Text.Trim和Text.Clean2个m函数)。

Excel新技能，你知道Excel中不可见字符，Excel里最隐蔽的坑