原标题:真实用! 一键清除不可见字符
在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盘用户用户名AppDataRoamingMicrosoftAddins,以后如需更新代码,可以在此找到。
5,关掉文件。
二、添加自定义功能区
1, 另外打开一个其他的EXCEL文档,点击文件-选项-自定义功能区-宏,在左侧找到添加的两个宏,并将它们添加到右侧的“自定义”区中(若无此区,可用新建选项卡-新建组),使用重命名,可以改变其图标。点击确定。
(在有的版本OFFICE中,“选项-自定义功能区-宏”的左侧不直接显示加载项,此时需要先从“选项-加载项”中选择“转到”先进性确定,才能完成上述操作)
2, 之后,在功能区的“自定义”选项之下,就有了所添加的按钮:
——Hoa小熊猫
责任编辑: