我恨你，63号不可见字符!，欢迎添砖加瓦。

人事难料

   今天一上班就让同事把上星期交给他的任务给我看一下，就是让丫把一些收集的资料放在一个查询表里。大家都知道网上直接复制的数据经常有不可见字符，一般用CLEAN()、查找替换或者分列就能解决，但是今天噩梦开始了。

用了多种办法依然无法清除这个字符，数字依然不能进行计算，用CODE查看后得知是ANCI代码"63"的字符，然后用CHAR（63）返回是个"?"，如果直接用？来进行查找替换，结果就是这个伟大的通配符同学把表里所有的数据全部清除。你这是要做化疗啊，通杀也没这样的吧。
因为数据量很大，且因为同事是从多个网站收集的资料，63号字符的位置出现次序都是不同的，而且还有部分数据是正常的，所有直接用substitute+mid去替换效果很差还容易误伤。
最后用了=MID($A3,COLUMN(A3),1)进行横拉把数字在每个单元格内单独列出，然后把63号字符所在单元格直接复制单元格，CTRL+H打开查找替换，在查找内容框里直接CTRL+V黏贴，点全部替换，386个内容被替换，世界总算清净了。
因为这个是特殊的不可见字符，不像空格之类的空字符选中以后明显会看见有变化，而这个63号间谍黏贴到查找框的时候你会以为什么都没黏贴进去，实际是已经可以进行下一步的替换工作了。
论坛里面搜索这个清除不可见字符帖子里有一个和我类似的情况，当时有一位老师给出的公式是=substitute(a3,"","")，实际上这个公式是对的，但是很容易让人误解的是这个公式你如果直接这样书写是得不到结果的。可以看帖子内F3和G3两个单元格内的公式，看上去一模一样，实际上G3单元格里第二参数不是直接书写的，而是复制了G7单元格产生的内存数组中第六个""黏贴而成的。
今天为这个挺遭罪的，论坛也没搜到很合适的解决办法，所以把我摸索的这些微不足道的经验告诉大家，也许可以帮助大家少走些弯路。
完工，吃饭去了，看到有错别字的自行脑补一下吧，哈哈。
63号字符.rar (13.73 KB, 下载次数: 530)

2014-4-8 11:57 上传

点击文件名下载附件

四楼提供思路不错，

1. =-LOOKUP(,-LEFT(A3,ROW($1:99)))

复制代码

针对纯数字的数据且不可见符号在最后位置的管用，其实扩展来说不管数字后面是符号或者文字都没关系，反正就是取前面出现的数字了，然后用两个减号转换为数字。
六楼准提老师的数组公式很棒，可以忽略不可见字符所在位置皆可剔除，就是需要先用CODE()得知该字符的ANCI码，不过这个很简单。公式如下

1. =--SUBSTITUTE(A3,MID(A3,MATCH(63,CODE(MID(A3&"?",ROW($1:$9),1)),),1),)

复制代码

七楼的水龙王星光大侠用的基础操作方式简单快捷，就是我之前写的复制出这个不可见字符然后替换。哈哈
详细可以看七楼的动画。

十四楼GOODLONG提供了一个复制不可见字符的办法，大家可以参阅一下，另外在十五楼提供另外一个帖子中解决这些字符的小技巧，我节选部分黏贴过来，具体内容可以点击十五楼的链接去看看。
此段文字版权归属frank55555同学，如有侵权处请告知删除：
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),"")


19楼祝老师也赞助了一个方法，把有问题的数字全部复制到word里面，然后打开查找替换窗口，查找栏输入[!一-龥^1-^127]，全部替换后再把数字复制黏贴回EXCEL。
我百度了一些关于这个方法的原理和知识，简单的说一下，这段字符在word里面代表了所有的中文字符，所以可以一次性的清除所有不利于计算的特殊字符。
祝老师在19楼上传了具体操作动画，大家可以参阅。


20楼lm1221同学也赞助了一个vba做法，不过这个是我绝对的软肋，看不懂也不理解做法，所以对lm1221同学说声对不起，只能推荐你的做法，但是无法为你做成说明。lm1221同学提供了这个代码的解释，但是我作为一个资深vba盲依旧表示看不懂。下述文字版权归属lm1221同学，如有侵权处请告知删除：
                        代码的主要部分为:"正则表达式,仅保留的其中的0-9数字与小数点",其余只是循环写入单元格的代码

24楼xpm同学给出了一个链接，也是用vba代码解决大部分字符的方式，软肋软肋。直接发上链接大家可以去看看，我实在解释不了。http://club.excelhome.net/thread-1098949-1-1.html

25楼cleverzhzhf 老师提供了一种substitute的简写方式，不过对于老师提供的截图我无法解释，因为在我电脑上没看见这种情况，大家可以在自己的机器上看看。

1. =SUBSTITUTE(A3,"",)

复制代码

这张写法同样要注意，中间这""不是直接书写的，还是需要先复制不可见符号然后黏贴进去才行，直接书写还是没用的。

zhaoxx

感谢版主  好好学习

人事难料

zhaoxx 发表于 2014-4-8 12:02
感谢版主  好好学习

兄弟，你是想借刀杀人吗。
我上个星期才刚靠辛勤灌水拿到了高级会员，你给我套个版主的帽子，我真心没啥对不住你的地方啊。

qigengzier

=-LOOKUP(,-LEFT(A3,ROW($1:99)))
这样也不长

人事难料

qigengzier 发表于 2014-4-8 12:42
=-LOOKUP(,-LEFT(A3,ROW($1:99)))
这样也不长

这个办法很不错思路巧妙，偷学一招。比我当时用=--LEFT(A3,LEN(A3)-1)的公式实用性强，我这个公式万一没有字符就会出问题了。
不过只能针对不可见字符在最后一位，实际上我自己的表格里这个字符出现的位置是不固定的，所以这个也不行。
不过借老师这个公式，我打算回头整理一下各种消除字符的方式，然后汇总成一个帖子。
谢谢老师没浪费我扔出来的这块破砖。

准提部林

＜不可見字元．不只一種＞
数组公式：僅適用于〔純數字+不可見字元〕
=--SUBSTITUTE(A3,MID(A3,MATCH(63,CODE(MID(A3&"?",ROW($1:$9),1)),),1),)
　
參考附檔：內有２種不可見字符
Xl0000687.rar (5.57 KB, 下载次数: 75)

2014-4-8 12:51 上传

点击文件名下载附件

　

看见星光

哈哈，公式入魔了你，这种东西，直接编辑替换就好。

itk50

看起来很复杂啊

人事难料

看见星光 发表于 2014-4-8 12:52
哈哈，公式入魔了你，这种东西，直接编辑替换就好。

星光兄，你这个办法很不错，能告知原理吗。
我还得靠mid分离出这个字符复制黏贴到查找替换框里才成功，没想到你这更简单，但是看的我不明觉厉啊。

看见星光

人事难料 发表于 2014-4-8 13:29
星光兄，你这个办法很不错，能告知原理吗。
我还得靠mid分离出这个字符复制黏贴到查找替换框里才成功，没 ...

没啦。就是复制单元格里的特殊字符，编辑替换。类似你所说的substitute函数，只不过没用函数，而是直接编辑替换啦。