全球頭條：去除Excel中的不可見字符，就這么幾步……

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

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

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

【資料圖】

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

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

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

1.空格去除

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

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

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

替換效果

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

2.去除不可見字符

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

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

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

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

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

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

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

2.1 定位不可見字符

「通過光標依次移動來判斷不可見字符位置」

雙擊單元格，進入數(shù)據(jù)編輯界面，此時看到閃動的光標。按鍵盤上的右方向鍵，依次向右移動光標。

如果明明按了右方向鍵，光標卻沒有移動，則說明這里存在一個不可見字符。

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

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

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

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

數(shù)組公式如下：

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

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

需提前選中B1:M1區(qū)域，因為需要承接拆分的字符，可以盡可能大一點。

再輸入數(shù)組公式

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

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

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

使用CTRL+H替換非?？旖?，但是如果數(shù)據(jù)是身份證號碼或長度大于11位的數(shù)字，一旦去除不可見字符，可能會導致格式直接變成科學計數(shù)，導致數(shù)據(jù)丟失。

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

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

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

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

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

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

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

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

紅框中的文本格式是通過在單元格前加"單引號構成的，格式刷后會直接在身份證號前面也添加一個單引號，因此不會變形。

總結(jié)一下

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

定位不可見字符，復制它

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

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

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

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

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

Len函數(shù)返回字符個數(shù)

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

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

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

看到這個了嘛，求一個

關鍵詞： 去除Excel中的不可見字符 就這么幾步…… excel