粉絲群里有同學(xué)遇到了表格中不可見字符問題，今天一篇文章教你各種去除不可見字符方法。

先說下為什么會(huì)出現(xiàn)不可見字符：

大多是從某些系統(tǒng)里下載導(dǎo)出的Excel表格，部分日期或者數(shù)據(jù)，由于編碼格式等問題，產(chǎn)生了不可見字符或者空格。

?通常出現(xiàn)在字符串的首尾。

導(dǎo)致的后果有vlookup無法正確匹配，函數(shù)公式或者計(jì)算操作無法正常進(jìn)行等等。

「去除異常字符是我們進(jìn)行數(shù)據(jù)清洗中的重要一環(huán)」

1.空格去除

例如這種下載的數(shù)據(jù)中每個(gè)姓名之間存在空格，可以通過替換法或者trim函數(shù)剔除。

替換法會(huì)將數(shù)據(jù)中所有空格全部替換為空，trim函數(shù)會(huì)至少保留字符之間的一個(gè)空格，并去除左右空格。

替換法選中需要替換的數(shù)據(jù)區(qū)域，按CTRL+H打開替換窗口，查找值輸入空格，替換值不輸入，全部替換，則去除數(shù)據(jù)中的所有空格。

替換效果

Trim函數(shù)則直接使用=trim(單元格)即可返回去除多余空格的數(shù)據(jù)。

2.去除不可見字符

不可見字符分兩種情況，一種是非打印字符。

以ASCII碼表為例, ASCII碼值在0-31的為控制字符, 無法顯示和打印，比如回車鍵。

如果你覺得表格中存在非打印字符，可以復(fù)制表格數(shù)據(jù)粘貼到TXT記事本中，如果出現(xiàn)其他字符和空格，則代表存在非打印字符。

Excel中去除方法很容易，使用CLEAN函數(shù)直接去除即可。

使用方法與上文的Trim函數(shù)一致。

另一種就是使用clean函數(shù)無法去除的不可見字符。比如下圖，使用clean函數(shù)后仍然顯示字符數(shù)存在2個(gè)額外字符。

我們就可以采取替換法或者直接取值法來去除，不過首先需要先定位不可見字符，找到它。

2.1 定位不可見字符

「通過光標(biāo)依次移動(dòng)來判斷不可見字符位置」

雙擊單元格，進(jìn)入數(shù)據(jù)編輯界面，此時(shí)看到閃動(dòng)的光標(biāo)。按鍵盤上的??右方向鍵→，依次向右移動(dòng)光標(biāo)。

如果明明按了右方向鍵，光標(biāo)卻沒有移動(dòng)，則說明這里存在一個(gè)不可見字符。

由于不可見字符通常難以用鼠標(biāo)選取，則可以通過函數(shù)left、mid、right函數(shù)來直接提取。

例如上圖案例，我們發(fā)現(xiàn)第一個(gè)字符就是不可見的，直接在空白單元格輸入=left(A2,1)提取不可見字符。

接下來，只需要全部替換這個(gè)不可見字符為空值即可。

「通過數(shù)組公式來拆分字符串」

數(shù)組公式如下：

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

數(shù)組公式使用方法

1. 需提前選中B1:M1區(qū)域，因?yàn)樾枰薪硬鸱值淖址?，可以盡可能大一點(diǎn)。

2. 再輸入數(shù)組公式

3. 最后需要按數(shù)組確認(rèn)鍵CTRL+SHIFT+回車 確認(rèn)公式

通過數(shù)組公式直接拆分字符，可以精確看到空白字符的位置，接下來，復(fù)制字符去替換即可。

3.用substitute函數(shù)替換

使用CTRL+H替換非常快捷，但是如果數(shù)據(jù)是身份證號(hào)碼或長度大于11位的數(shù)字，一旦去除不可見字符，可能會(huì)導(dǎo)致格式直接變成科學(xué)計(jì)數(shù)，導(dǎo)致數(shù)據(jù)丟失。

因此，可以使用函數(shù)來實(shí)現(xiàn)精準(zhǔn)替換。

上圖可以是substitute函數(shù)的基本用法，直接使用left提取字符串第一位，也就是不可見字符來當(dāng)查找值，實(shí)際查找值位置要根據(jù)你的表格實(shí)際來調(diào)整。

或者也可以把不可見字符復(fù)制粘貼到記事本再復(fù)制回來，直接寫在公式里，記得加""號(hào)。

但是上面的公式并未成功替換不可見字符，準(zhǔn)確來說，只替換了一個(gè)，還剩結(jié)尾1個(gè)。

干脆點(diǎn)，就直接再嵌套1個(gè)substitute函數(shù)，此時(shí)結(jié)果如下。

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

substitute函數(shù)返回結(jié)果默認(rèn)文本，不用擔(dān)心格式變化。

如果你還是想用CTRL+H替換法，則需要提前用格式刷給身份證號(hào)碼刷一個(gè)文本格式。

紅框中的文本格式是通過在單元格前加'單引號(hào)構(gòu)成的，格式刷后會(huì)直接在身份證號(hào)前面也添加一個(gè)單引號(hào)，因此不會(huì)變形。

總結(jié)一下

?清洗Excel數(shù)據(jù)中不可見字符的主要邏輯：

1. 定位不可見字符，復(fù)制它

2. CTRL+H或者函數(shù)substitute替換

??還有兩個(gè)專門清洗空格和非打印字符的函數(shù)。

1. Trim函數(shù)去除多余空格

2. Clean函數(shù)去除非打印字符

??以及檢測字符數(shù)量和截取字符的函數(shù)：

1. Len函數(shù)返回字符個(gè)數(shù)

2. Left函數(shù)從左側(cè)截取字符

3. Mid函數(shù)從中間截取字符

4. Right函數(shù)從右側(cè)截取字符

??看到這個(gè)了嘛，求一個(gè)??