|
在EXCEL数据处理中，我们常常会受到不可见字符的干扰，就比如下面这个例子： 左看右看，公式也并没写错呀，可是，东坡君你肿么了？ 此时，作为一个冷静的侠客，手要稳，心要定，首先就要想到，A2和D1这俩格子内容是不一样的！哪里不一样呢？ 可以先用len函数看看长度，果然，D1是2字符，A2是3字符： 可是当我们从编辑栏仔细查看A2内容时，却是看不到“莫须有”的第三个字符在哪。 此时，作为一个有经验的侠客，就会使用left、right函数取出A2的首个、末尾字符，再用code函数看看，它是个什么。 果然，right(A2,1)的结果是无法看见的，且其code值是9： 于是我们知道了，A2中多出来的那个字符是char(9)，即制表符，这是很多从网页中粘贴出来的数据，常常会“携带”的多余字符。 如此例的情况，我们可以用left(A2,len(A2)-1)的公式方法，去掉这一列普遍携带的多余字符。
然而，其实实际中遇到的不可见字符并非总在首尾之类有规律的位置，也不一定只有一种，常见的，就可能有以下的类型（Char是ASCII码表值，EXCEL中可以用char公式查看）： Char(1)，空字符NULL Char(9)，制表符 Char(10)，换行符 Char(13)，回车符 Char(32)，空格 Char(160)，这是HTML中的一种空格，复制到EXCEL中也看不见 这么多种不可见字符怎么处理掉呢？ 虽然我们知道TRIM函数可以用来去掉多余空格（且留下英文单词中间该有的空格），但对其他情况可就不行了。 不过一定要用公式解决的话，也并非不可，因为，有一个叠加SUBSTITUTE的“可怕”方法： =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,CHAR(1),''),CHAR(9),''),CHAR(10),''),CHAR(13),''),CHAR(32),'') 这就叠了5个SUBSTITUTE，用来去掉单元格中的5种不可见字符~~ 是不是非常无语？ 于是呢，我们可以用加载VBA的方式，添加这样一个自定义功能： 如需去掉不可见字符，只要选中区域，点击“删除不可见字符”按钮，即可完成： 代码是比较简单的，其实就是把6次叠加substitute换成了VBA函数replace来完成： Sub 删除不可见字符() '此代码的附加功能：若遇到带公式的格子，会先转换为不带公式的值，再去掉不可见字符。 '有个bug，所有处理过的区域都会变成文本格式 Dim rng As Range, i, j, k, leng As Integer Dim r, c, s, ascma Set rng = Selection arr = rng r = rng.Rows.Count c = rng.Columns.Count rng.Interior.ColorIndex = xlNone '设置区域背景为无色 rng.NumberFormatLocal = '@' '设置区域格式为文本 If r = 1 And c = 1 Then arr = Replace(arr, ChrW(1), '') arr = Replace(arr, ChrW(9), '') arr = Replace(arr, ChrW(10), '') arr = Replace(arr, ChrW(13), '') arr = Replace(arr, ChrW(32), '') arr = Replace(arr, ChrW(160), '') Else For i = 1 To r For j = 1 To c '1、32是空格，9是制表符，10、13是换行符 arr(i, j) = Replace(arr(i, j), ChrW(1), '') arr(i, j) = Replace(arr(i, j), ChrW(9), '') arr(i, j) = Replace(arr(i, j), ChrW(10), '') arr(i, j) = Replace(arr(i, j), ChrW(13), '') arr(i, j) = Replace(arr(i, j), ChrW(32), '') arr(i, j) = Replace(arr(i, j), ChrW(160), '') Next Next End If rng = arr MsgBox '区域中的空格、制表符、换行符、char(160)都已删除！！！' End Sub 如何将它们添加到功能区呢？很简单： 一、写入加载宏文件。 1， 新建一个EXCEL工作薄，打开代码页面（在“开发工具”中点击Visual Basic或按快捷键Alt F11或右键任意工作表名-查看代码）； 2， 在代码页面中插入模块，模块名可自己更改； 3， 双击新建的模块，在右侧代码窗口贴入代码，关闭代码页面； 4， 点击文件-另存为，将文件存为“EXCEL加载宏”格式，即xlam格式（此格式有固定路径，无需选择路径）；一般来说，这个文件路径是在：C盘\用户\用户名\AppData\Roaming\Microsoft\Addins，以后如需更新代码，可以在此找到。 5，关掉文件。 二、添加自定义功能区 1， 另外打开一个其他的EXCEL文档，点击文件-选项-自定义功能区-宏，在左侧找到添加的两个宏，并将它们添加到右侧的“自定义”区中（若无此区，可用新建选项卡-新建组），使用重命名，可以改变其图标。点击确定。 （在有的版本OFFICE中，“选项-自定义功能区-宏”的左侧不直接显示加载项，此时需要先从“选项-加载项”中选择“转到”先进性确定，才能完成上述操作） 2， 之后，在功能区的“自定义”选项之下，就有了所添加的按钮：
——Hoa小熊猫 |
|
|
