html 显示不可见字符,真实用！ 一键清除不可见字符

原标题：真实用！ 一键清除不可见字符

在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小熊猫

责任编辑：