今天為大家介紹的是excel數據整理時一類最常見的坑——不可見字符。從unichar函數對應的uincode字符集結果來說，excel大約有111萬+個字符，其中有不少字符的性質千奇百怪，由于unichar/unicode函數出現得晚(2013版本新增函數)，因此現在還在用2010或以前版本的小伙伴們，如果經常需要網頁或軟件導出的數據，就可能會被一些看不到的字符影響統計和計算。接下來就和大家一起說說excel里各種常見的不可見的字符與對應解決方法。

1、文本識別符和空文本

字符串最前面的一個半角單引號。

?

嚴格來說，這個字符不屬于字符串內的字符，而是excel的特殊格式設定。在字符串前加上這個半角單引號會使整個字符串強制設置為文本格式，無視之前單元格格式的設定。

這個半角單引號的特點是只在編輯欄中顯示，單元格內不顯示。這個字符的存在不影響字符串匹配(除了數據類型差異)但是總有強迫癥患者問怎么清除這個字符，已經解釋了這字符屬于特殊格式設定。

因此不能使用字符的處理方式，需要使用格式的方式:

找個空單元格把格式用格式刷刷過來，或者使用開始菜單下右側編輯部分的清除格式功能清除這個字符。（注意，設置完成后，要雙擊單元格，單元格才能變成常規模式。）

?

另外一類不屬于不可見字符但經常混為一談的東西為空文本，即假空。

假空是和真空(單元格)相對的，共同點是使用len函數對這個單元格計算字符數時結果都是0（0字符）。

假空多由于公式結果或者軟件導出造成。len函數的字符計算結果是0，并且也無法通過定位到空值的操作，定位到相應單元格。

假空在公式里以""表示，因此本質還是文本，雖然無法用len函數檢測出來，但是可以用istext或者isblank等函數檢查出來與真空的差異。

注意countblank函數不區分真空與假空。

當然，也可以使用分列，進入分列頁面后直接點完成，把假空處理為真空。

或者使用查找功能，在查找框中什么都不輸，對查找到的單元格使用Ctrl+A全選來定位真空+假空單元格，用于后續處理(選中后右鍵清除內容即可把全部假空單元格處理為真空)。

?

上面這兩種不屬于字符但經常被混為一談(因為這2種不能被len函數檢查出來)所以先介紹下。

然后我們就來聊聊excel里各種影響常規匹配與核對/計算的各種不可見字符了。

2、空格

這應該是最容易發現的不可見字符吧。

?

肉眼只能看到兩個字符但len結果為3，所以存在不可見字符。

空格是占字符寬度的，因此可以在編輯欄內直接選中抹黑查看。

?

code函數對空格返回的字符編碼結果為32，可以用這個函數快速定性，清除方式可以直接替換為空(什么都不輸)。函數處理如果為兩側空格可以用trim函數快速清除(這函數不能清除中間作為間隔符的空格，中間的要用substititute+char(32)清除。(記住trim函數只能清除兩個字符:空格與其全角形式(char(32)與char(41377)))。

3、非打印字符

這幾個字符里大家比較熟悉的是char(10)換行符。屬于常見的非打印字符，但對新手來說這些字符比空格相對隱蔽。因為像char(9)這類字符在單元格內是不占寬度的，所以無法直接使用抹黑方式檢查。

江湖上有一招叫照妖鏡就是針對這類性質，即把字符串復制粘貼在記事本或者word將字符現行，再復制粘貼進行替換清除。

?

粘在記事本里能發現存在多余字符。

?

除此之外，非打印字符也可以使用分列功能清除，函數里通常使用clean函數直接清除這部分字符。

?

然而clean的清除能力是有限的，只能清除code結果為char(1-31，128)合計32個字符，。

如果用unicode函數則結果為unichar(1-31，128-159[這31個在中文語言下code的結果是0])范圍合計63個字符。

綜上所述，其實trim和clean的清除能力是很有限的(不可否認能清除的都是不可見字符類型里的常見字符)。

還有很多字符是這兩個函數清除不了的，最典型的是unichar(160)，這個字符在微軟的trim函數幫助內有提及，不間斷空格字符，常見于網頁。

在單元格中的性質和空格很相似但無法用trim清除，這個字符的快速清除方式為直接復制后替換為空(什么都不輸)清除。

這個字符對新手來說最大的誤區在于用code函數獲取的編碼值為63，但不能使用substitute+char(63)清除。

而code結果為63的不可見字符，是新手最容易出現的誤區，也是這篇文章的重點內容。

4、code結果為63的不可見字符

由于早期版本函數code與char的不完全逆運算以及使用的字符集數量有限，code函數會把所有不識別的字符全部默認為 ? ，因此code結果都是63。

而遍歷下來code結果為63的字符大約有108萬+個(你沒看錯數字，code結果不為63的字符只有3萬+)。因此使用clean與trim都無法清除的不可見字符，通常code結果都是63。

如果該字符類似空格在單元格內有寬度，可以直接復制替換清除。

如果類似char(9)的性質，無法抹黑發現，通常準確的清除方式為使用unicode函數獲取對應編碼后使用unichar+substitute函數清除。

關于這部分網上也有很多代碼，但測試下都沒有能100%清除干凈的，和代碼本身無關，重點是要有全部的不可見字符編碼表，但這個會涉及到很多環境因素影響，例如字體等都會影響某些字符的可見性)。

?

因此如果在不可見字符規則不規范的情況下處理這類字符的最準確方式為確認對應unicode編碼后使用函數/vba/pq等替換/移除來清除。此類不可見字符里最危險的字符為unichar(8204)，unichar(8205)等等。

因為這批字符有幾個特殊性質:

?

1，code結果為63。

2，單元格內無寬度。

3，在記事本與word里也不會現行。

4，trim與clean函數不能清除。

5，這些字符雖然占字符數但在用等號判斷時不影響結果(圖里紫色的公式)。

這類字符最大的特性在于第5條，excel里只有3709個字符有此性質(365版本下有老師測試過結果為2000+)。

這個判斷性質對于新手極其容易造成核對時出現錯誤。

5、新版本，新工具

5.1 pq的不可見字符清除

上面介紹的是各種不可見字符與其清除方式。大家可以發現直到2013版本出現unichar與unicode函數后這種問題才能有效準確解決。然而除了這2個新函數，高版本的新工具在一定程度上也能有效處理這類問題。Power query 內的轉換-文本列-格式內有2個功能(修整，清除)(對應Text.Trim和Text.Clean2個m函數)。

?

其中清除(Text.Clean)能力和工作表函數clean能力一致。但Text.Trim能清除的字符比工作表函數trim多太多了(注意是兩側的)。

?

這說明微軟一直知道某些不可見字符處理的常見坑但是在工作表函數范疇一直沒解決，在pq里才有一定程度的處理(注意還是不能清除上述最特殊的unichar(8204)等字符)。

可以使用Text.Remove+Character.FromNumber 清除。

5.2，應用商店

2016自帶的應用商店里有不少實用小工具，如XLTools.net Data Cleaning，專門清除和轉換字符的數據類型。

測試下能清除unichar(160)等不可見字符，當然還是不是萬能的，但是操作不難，適合新手使用，重點是上述最危險的unichar(8204)這種字符也能被直接清除。

調用方式為2016的插入-加載項的應用商店里搜索或在分類中找到該工具添加，在”我的加載項”內調用。

?

調用后excel界面右側出現對應菜單。

?

Step 1選取處理區域。

Step2選擇要清除的不可見字符類型(兩端空格，不相干的干擾字符，換行符，非打印字符等等)。

雖然極少數偏僻字符也不能處理，但已經能把絕大多數干擾字符清洗干凈了(測試下能清除8000+個字符)。

這就是高版本帶來的好處，低版本難以實現的問題我們經�？梢越柚�高版本的新工具快速解決了。