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

先說下為什麼會出現不可見字符：

大多是從某些系統裡下載導出的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)

數組公式使用方法

1. 需提前選中B1:M1區域，因為需要承接拆分的字符，可以盡可能大一點。
2. 再輸入數組公式
3. 最後需要按數組确認鍵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數據中不可見字符的主要邏輯：

1. 定位不可見字符，複制它
2. CTRL H或者函數substitute替換

還有兩個專門清洗空格和非打印字符的函數。

1. Trim函數去除多餘空格
2. Clean函數去除非打印字符

以及檢測字符數量和截取字符的函數：

1. Len函數返回字符個數
2. Left函數從左側截取字符
3. Mid函數從中間截取字符
4. Right函數從右側截取字符

看到這個了嘛，求一個

,