粉丝群里有同学遇到了表格中不可见字符问题，今天一篇文章教你各种去除不可见字符方法。

先说下为什么会出现不可见字符：

大多是从某些系统里下载导出的Excel表格，部分日期或者数据，由于编码格式等问题，产生了不可见字符或者空格。


【资料图】

通常出现在字符串的首尾。

导致的后果有vlookup无法正确匹配，函数公式或者计算操作无法正常进行等等。

「去除异常字符是我们进行数据清洗中的重要一环」

1.空格去除

例如这种下载的数据中每个姓名之间存在空格，可以通过替换法或者trim函数剔除。

替换法会将数据中所有空格全部替换为空，trim函数会至少保留字符之间的一个空格，并去除左右空格。

替换法选中需要替换的数据区域，按CTRL+H打开替换窗口，查找值输入空格，替换值不输入，全部替换，则去除数据中的所有空格。

替换效果

Trim函数则直接使用=trim(单元格)即可返回去除多余空格的数据。

2.去除不可见字符

不可见字符分两种情况，一种是非打印字符。

以ASCII码表为例，ASCII码值在0-31的为控制字符，无法显示和打印，比如回车键。

如果你觉得表格中存在非打印字符，可以复制表格数据粘贴到TXT记事本中，如果出现其他字符和空格，则代表存在非打印字符。

Excel中去除方法很容易，使用CLEAN函数直接去除即可。

使用方法与上文的Trim函数一致。

另一种就是使用clean函数无法去除的不可见字符。比如下图，使用clean函数后仍然显示字符数存在2个额外字符。

我们就可以采取替换法或者直接取值法来去除，不过首先需要先定位不可见字符，找到它。

2.1 定位不可见字符

「通过光标依次移动来判断不可见字符位置」

双击单元格，进入数据编辑界面，此时看到闪动的光标。按键盘上的右方向键，依次向右移动光标。

如果明明按了右方向键，光标却没有移动，则说明这里存在一个不可见字符。

由于不可见字符通常难以用鼠标选取，则可以通过函数left、mid、right函数来直接提取。

例如上图案例，我们发现第一个字符就是不可见的，直接在空白单元格输入=left(A2,1)提取不可见字符。

接下来，只需要全部替换这个不可见字符为空值即可。

「通过数组公式来拆分字符串」

数组公式如下：

=MID(A1,TRANSPOSE(ROW(1:12)),1)

数组公式使用方法

需提前选中B1:M1区域，因为需要承接拆分的字符，可以尽可能大一点。

再输入数组公式

最后需要按数组确认键CTRL+SHIFT+回车 确认公式

通过数组公式直接拆分字符，可以精确看到空白字符的位置，接下来，复制字符去替换即可。

3.用substitute函数替换

使用CTRL+H替换非常快捷，但是如果数据是身份证号码或长度大于11位的数字，一旦去除不可见字符，可能会导致格式直接变成科学计数，导致数据丢失。

因此，可以使用函数来实现精准替换。

上图可以是substitute函数的基本用法，直接使用left提取字符串第一位，也就是不可见字符来当查找值，实际查找值位置要根据你的表格实际来调整。

或者也可以把不可见字符复制粘贴到记事本再复制回来，直接写在公式里，记得加""号。

但是上面的公式并未成功替换不可见字符，准确来说，只替换了一个，还剩结尾1个。

干脆点，就直接再嵌套1个substitute函数，此时结果如下。

=SUBSTITUTE(SUBSTITUTE(B2,LEFT(B2,1),""),RIGHT(B2,1),"")

substitute函数返回结果默认文本，不用担心格式变化。

如果你还是想用CTRL+H替换法，则需要提前用格式刷给身份证号码刷一个文本格式。

红框中的文本格式是通过在单元格前加"单引号构成的，格式刷后会直接在身份证号前面也添加一个单引号，因此不会变形。

总结一下

清洗Excel数据中不可见字符的主要逻辑：

定位不可见字符，复制它

CTRL+H或者函数substitute替换

还有两个专门清洗空格和非打印字符的函数。

Trim函数去除多余空格

Clean函数去除非打印字符

以及检测字符数量和截取字符的函数：

Len函数返回字符个数

Left函数从左侧截取字符

Mid函数从中间截取字符

Right函数从右侧截取字符

看到这个了嘛，求一个

关键词： 去除Excel中的不可见字符 就这么几步…… excel