[字符系列1] char与code的恩怨情仇

流浪铁匠

学习函数以来，有很多有意思的发现，今天介绍一组可以相互转化的函数                                
所谓相互转化，就是其中1个函数的结果可以套嵌另1个函数来转化回来，或者称为逆运算,譬如RADIANS和DEGREES，DEC2BIN和BIN2DEC等等                                
但是和其他搭档函数相比，今天介绍的这组函数，是不能完全一一对应转化的                                
                                
Ps:阅读本文如需自行测试,请使用至少2013版本,因为涉及该版本新增函数unichar和unicode                                
为便于新手测试,所有公式均使用可以不三键的方式书写,由于借助整列来获取大量数字遍历,请使用xlsx格式的工作簿                                
rows(row(a:a))是之前遍历统计时为自动获取行数1048576所用,由于测试时一直沿用原思路遍历,统一公式结构,故后续未进行简化，自行测试时建议把公式直接改成1048576                                
测试环境：excel2016,默认语言中文(本文使用的部分函数在默认语言变化时有时结果有差异,所以说明下),下述无特殊说明时默认该环境                                
从函数帮助说明来看,char是将数字代码转化为对应字符，code是将单个字符转化为对应的数字代码                                
由于种种原因，这2个函数，实际测试下来并无法 一 一对应转化，也造就了不可见字符处理中的一大类坑，所谓的char(63)[后面对这个问题有详细描述]                                
本人对各类字符集没什么研究，一切测试都是在excel上完成的，因此，主要就是聊聊这2个函数和它们衍生的一系列问题                                
                                                        
目录篇                                
第1部分: char 与 code的恩怨情仇                                
char函数特性                                
5类不对应字符:char(32),char(63),char(0),char(128),char(255)                                
第2部分: 颠覆字符概念的unicode字符                                
第3部分: trim PK clean                                

附件为本文原稿
char与code的恩怨情仇 by 铁匠.rar (122.21 KB, 下载次数: 168)

2018-2-19 11:03 上传

点击文件名下载附件

流浪铁匠

char   特性: 1,取整;2,有盲区                                       
1,取整:char函数内的参数,函数帮助介绍是1-255,但实际测试下来最大值为65535     
                     
而且作为参数的正数具有自动取整性，参数具有小数时会自动取整识别                                        

2,盲区:  称为盲区,是因为在1-65535范围内,char函数并不能产生65535个字符,遍历测试下来为32766个
                                       
即1-65535范围内不到一半的数字有对应的ANSI字符                                       
但至少远远超过函数帮助介绍中的1-255范围                                       
而且,即便是这3万多个字符,用code获取的对应数字，也不能一 一 对应:                                       
因为这个性质,故视为这个函数的转化范围是存在盲区的                                       
测试确定char的准确取值范围是1-255和33025-65535两组,而中间间断的256-33024范围即为盲区                                       
         

以下为探索中发现的5组 code和char不能对应 一 一 转化的特殊字符系列                                       
不对应字符系列1——空格型                                       
char(32)和char(129-254)这127个字符，对应的code/unicode值均为32(空格的代码)，且均能用trim和substitute+char(32) 清除                                       
以上为仅用char和code函数就可以发现的不完全对应的字符                                       
由于早期版本函数缺失,借助2013版新增的unichar和unicode函数,对于字符的对照，有了新的发现，如下所述                                       
                                       
不对应字符系列2——估计是大家最熟悉的不可见字符类型，所谓的char(63)                                       
本文的重点也是这个                                       
由于unichar/unicode这对函数在2013版才出现                                       
早期版本由于无法深究这个(类)字符,之前看过几位前辈的帖子,也有介绍过这个字符,结论基本是对于code值为63的不可见字符，推荐使用替换大法处理                                       
因为                                       
在我的2016版(默认语言中文)，一共有1087896个数字使用unichar函数获取的字符,用code获取的数字代码是63 !!!                                          
疑似是因为Excel把所有无法用code识别的字符均默认为"?",而"?"的code值即为63的原因                                       
                                 
即，在excel内，有100+万个字符对应的code值为63                                       
有意思的是，在别人建议下，我把默认语言改为英文后，结果如下                                       
                                 
即在不同语言环境下，结果出现差异的函数不仅仅只有lenb/leftb这种                                       
这类字符转化函数同样存在该问题                                       
            
无论结果是哪个,都说明了,在excel内,code函数都会将当前环境下无法识别的字符的对应代码产生为63                                       
而且总数在100+万个以上,因为这100万+个字符本身并不一样，当然无法直接使用substitute+char(63)清除                                       
而同样遍历测试,excel内unicode字符集共计可产生1111997个字符                                       

code值为63的字符占unicode字符总数的97.83% (1087896/1111997)                                       
在我看来,code值为63的不可见字符相对其他不可见字符难以处理的原因就在这里                                       
纯函数解法,必须先知道这个字符究竟是这100万+个字符中的哪一个,2013版可以用unicode直接确定                                       
但早期版本没有unicode，而code的结果只会返回63，误导了很多人，这也是为什么推荐替换大法的原因，直接有效(少量字符在编辑栏内宽度疑似0,无法直接使用替换方式处理,要先粘在word或记事本现原形)                                       
在这100多万个字符中,提一个比较出名的:在unichar(1-255)范围内,有1个unichar(160) 是不可见的,code值也是63,                                       
而且,这是我在微软函数帮助内唯一发现被介绍的特殊字符(trim函数介绍内),被称为"不间断空格字符",常见于网页                                       
                                       
不对应字符系列3——很多人不知道的char(0)                                       
和char(63)一个意思,指的是该字符用code获取的数字结果为0                                       
这类字符一共发现31个,是之前遍历测试时发现的,为unichar(129-159)合计31个unicode字符                                       
直接 =char(0) 是返回错误值的,所以这组字符也是有意思的一组,因为0不在char的数据范围内,但code的结果可以产生                                       
由于这31个(不可见)字符均可使用clean直接清除,所以很多人可能不知道其存在                                       
            
不对应字符系列4——char(128)                                       
code值在1-255之间的字符遍历统计,除了上述,有2组挺有意思                                       
unicode字符集中有2个字符对应的code值均为128(unichar(128)和unichar(8364)),                                       
         
而且有意思的是,这2个字符，1个可见，1个不可见，由于unichar(8364)是可见的,这是最不需要关注的一组特殊字符                                       
                                       
不对应字符系列5——继char(63)后的最大发现:char(255)                                       
和系列4一样,unicode字符集中有2个字符对应的code值均为255(unichar(255)和unichar(63733)),而且也是一个可见,一个不可见                                       
但在char对应的ANSI字符集内,char(255)和char(65280-65535)合计257个字符,用code值获取的结果均为255                                       
而char(65280-65535)这256个字符用unicode获取的结果均为63733,且均为不可见的                                       
和code值为63的不可见字符性质一样,无法使用clean/trim/substitute+char(255)等方式清除!!!                                        
测试后只能使用substitute+unichar(63733)的方式清除,也是一组有意思的字符                                       

                              
以上是对code值在0-255之前的字符利用公式遍历测试发现的一些特殊字符                                       
足以表明,char和code函数是无法完全相互转化的,部分字符的识别有很大差异                                       
而且,unicode字符集代码为32/63733这2种情况下char产生的字符和unicode也不是一一对应了,                                       
此时使用char函数产生的字符和unicode字符代码对比是多对一的关系                                       

流浪铁匠

第2部分                                                
颠覆字符概念的unicode字符                                                
unichar函数产生的所有字符,有1048544个的len结果为2,而产生的结果为1个字符的为63453个,二者总数与上述unicode产生的字符总数1111997一致      
而遍历测试下char函数产生的字符无此性质,len结果均为1                                                
                        
也就是说,unicode字符,在excel中len的结果不一定是1                                               
         
而且len和lenb的结果都是2(默认语言中文)，这个结果与字节/字符的概念存在很大差异                                                
因为从正常情况考虑,产生的单个字符,len的结果无论处于什么环境,都应该是1                                                
百度后发现应该是"Unicode 给地球上所有的字符一个唯一的编号，                                                
严格来说,Unicode 是没有“长度”这一说的,它是抽象的字符,只有Unicode的编码才有具体的字节长度。"的原因                                                
这个性质,可能会对字符数的判断/不可见字符的查找确认等情况有所影响                                                
为了判断这类情况，计算了下满足len=2的unicode的数值范围,最小值为65536                                                
  
且65536以上的unicode字符的len结果均为2                                                
                                                
在一个字符串中,如果需要检测是否存在这类性质的unicode字符,需要借助文本函数挨个字符提取判断                                                
但此时又发现了left/right和mid的一点区别                                                
曾经在季度那题的末尾,介绍过函数参数为小数时的舍入点区别                                                
                        
mid的2/3参是严格取整的                                                
但right/left的2参的舍入临界点都是0.99999976146501                                                
在利用unicode重新确认不可见字符类型时,一样发现了这2组函数的区别                                                

这个测试结果表明了2件事:                                                
1,mid和 left/right 的提取机理存在很大差异,不仅仅是小数点修正的问题了,对字符的识别性质也存在差异                                                
2,这类unicode代码值大于65535的unicode字符和正常的文本字符存在差异,这应该也是造成len结果为2的原因                                                
因此,使用len判断不可见字符的方式,可能会由于这类字符的特性而受到影响,需要先测试字符串内有无unicode值大于65535的存在                                                
暂定使用公式=COUNT(0/(UNICODE(RIGHT(LEFT(A156,MMULT(ROW(INDIRECT("1:"&LEN(A156))),1))))>65535))                                                
如果字符不长,row(indirect())可以直接换成row(1:50)等，自行修改单元格即可                                                
该公式结果大于0，则说明存在len结果为2的unicode字符                                               
(mmult的存在主要是为了不三键,indirect是为了尽量减少运算量……高手请自行简化公式)                                                
当然,一般情况下是不需要考虑这个情况的        

流浪铁匠

第3部分
trim PK clean
还是老方法,遍历方式测试
clean可以清除char(1-31),char(128)合计32个ANSI字符。
用unicode字符集看是unichar(1-31),unichar(128-159)合计63个unicode字符
以上ANSI字符的code/unicode函数结果与所列数字一致
但所列unicode字符,unicode函数结果与所列数字一致,但code函数结果1-31与128时一致,129-159时code结果为0,即为上面所介绍过的char(0)

trim可以清除(因函数特性,只考虑两端完全清除) char(32),char(129-254)合计127个字符。
用unicode字符集看是unichar(32)和unichar(12288)合计2个unicode字符
以上字符中除unichar(12288)外,其他字符的code与unicode函数结果均为32,而unichar(12288)的code值为41377,unicode结果即为12288
发现这点后感觉char(32)有点意思,127个char函数产生的字符,用unicode获取的结果全部是32,为目前唯二发现的char比unichar多的情况(另一组见上面的char(255))

综上所述,这2个函数,对不可见字符的清除能力有限
char(1-255)之间的char(127)这1个字符
以及char(65280-65535)这256个code获取代码均为255而unicode对应代码均为63733的字符
clean和trim两个函数都无法清除,更不用说大量code函数结果为63的不可见字符
但是,了解这2个函数的能力范围,还是有必要的,有助于我们在合适的场合使用合适的函数

流浪铁匠

每日图片上传数量有限,系列2不可见字符处理篇明日再上传

abuding

楼主 威武，治学严谨！ 佩服

ebkzxcel

楼主 威武，治学严谨！ 佩服1

624134745

学习了，支持楼主认真钻研的精神