char(63),你不是一个人在战斗(不可见字符处理的一点发现)

流浪铁匠

诸位老师好，在涉及不可见字符的处理中
相信很多人都见过下面这段结论
1、代码值(用CODE函数得到)在1-255范围内的字符中,有1-15,28-32,127-254共148个不可见字符。
2、其中代码值小于等于31的(含16-27的可见字符)及代码值等于128的字符均可用CLEAN(A1)函数清除。
3、代码值等于32及129-254的不可见字符,均可用SUBSTITUTE(A1,CHAR(32),"")函数清除，这类字符如果在字符串两端可用TRIM函数清除。
4、代码值等于127的不可见字符只可以用SUBSTITUTE(A1,CHAR(127),"")函数清除。
5、同时使用两个函数,基本上可清除代码值小于255的所有不可见字符(含代码值为16-27的可见字符):
SUBSTITUTE(SUBSTITUTE(CLEAN(A1),CHAR(32),""),CHAR(127),"")


但是，在很多帖子中都有提到一个难以处理的字符：code值为63，很多人直接使用substitute+char(63) 是无法清除这个不可见字符的
很多老师也是建议在字符串中复制这个不可见字符来直接替换处理

这个字符一直让我很困惑，直至使用了2013的新增函数unichar和unicode函数，才揭开这个字符的一点真相：
由于unichar/unicode这对函数在2013版才出现，早期版本由于无法深究这个字符，造成这个不可见字符代码在早期版本有一定误导性
使用unichar函数从1开始遍历，产生所有字符
产生的各字符，一一使用code获取对应的char值
发现了一个很可怕的事情：
在我的2016版，一共有1087896个数字使用unichar函数获取的字符,用code获取的数字代码是63 !!!
=COUNT(0/(CODE(UNICHAR(ROWS(ROW(A:A))*{0,1,2,3,4}+MMULT(ROW(A:A),1)))=63))
想自己尝试的，可以自己使用2013及以上版本(建议使用xlsx格式)使用上述公式看看返回结果,这个公式我有调整，可以不用三键，避免新手不知道三键造成不能返回正确结果

也就是说，char(63) 能够对应的字符数量，Excel 一列都不能全部容纳!
还是借助上述思路
unichar产生的字符，能用code获取数字代码的有 1111966 个
char(63) 占到上述数量的 97.8%
如果从占比考虑，这个不可见字符出现的几率是最大的
但是，由于Excel的unicode字符集和char字符集有一定差异
在以前的字符处理经验中，code获取的不可见字符代码为63时，很多时候无法使用substitute+char(63) 替换处理
就是这个原因，因为太多的字符的unicode值是不一样的，但code值均为63
仅1-255对应的unicode字符中,unichar(160)这个的code结果就是63,且也是不可见字符,但unicode(160)无法使用clean,trim,substitute+char(63)等各种常规函数手段处理

流浪铁匠

另外在整理这些字符时发现另外一件事：
在整理char(63)时，借助遍历的方法使用unichar罗列所有字符,分别用code函数和unicode函数获取数字代码
发现unicode 获取的正数代码有 1111997个，但用code 获取的正数代码有 1111966个，少了31个
遍历判断后发现为 unicode代码为129 -159 的31个字符,这31个对应的unichar字符,使用code获取的数字代码均为0(这公式思路不难,与上面公式类似，由于使用遍历运算量很大，所以不再罗列)
但char(0)的结果是错误值，这是除了char(63)和code 不能完全相互转化以外的另一组特殊字符
这系列字符和char(63)的情况类似,无法直接使用substitute+char 处理
不过测试发现这31个字符均可使用clean函数清除，相对容易处理，但使用code函数判断时可能受到误导
这是在对比这类字符时发现的一处漏洞

流浪铁匠

因此
char(63) 这个字符，在2010和以前版本，建议使用复制替换的方式处理
2013开始的新版本，就可以使用unicode/unichar配合substitute纯函数方式处理了

cabcyvr

建议在标题上加个额外说明，（只适用于中文版）

流浪铁匠

cabcyvr 发表于 2017-7-12 06:12
建议在标题上加个额外说明，（只适用于中文版）

没找到用英文版的朋友，不过在别人建议下我切换默认语言测试了下
遍历统计思路：=COUNT(0/(CODE(UNICHAR(ROWS(ROW(A:A))*{0,1,2,3,4}+MMULT(ROW(A:A),1)))=63))
默认中文时，我的2016中这个公式的结果是1087896
默认英语时，我的2016中这个公式的结果是1111837
所以在其他语言环境下，char(63) 应该也是存在这个问题的

流浪铁匠

尝试上图