上月收到一個財務同事的Excel求助：

同事J：“收到的表格里有隱藏的空格和雙引號” “，在Excel里不顯示，數據透視表求和也不正確。目前我們只能通過複製到text文檔。雙引號“”可以通過替換功能全部清除，空格手動一行行刪，再粘回excel。很笨很低效率。這個問題已經困擾我們幾年了。大神能不能幫我們看看。”

情況分析

原始Excel表顯示如下：

圖1 – 原表格看不見隱藏字符

數據透視表報錯“值不可用”錯誤，顯示如下：

圖2 – 透視表顯示錯誤

原因分析

複製粘貼到文本文檔里顯示如下，發現付款人名稱字段裡面包含了空格和雙引號：

圖3 – 複製到文本文檔查看

這才是真正的完整數據，而在上圖Excel里空格和雙引號被隱藏了。這種情況實際上是“非打印字符”惹的禍。

再到站長之家裡用Unicode編碼轉換工具驗證一下這些字符，直接複製單元格數據到左側的框里，然後點“ASCII轉Unicode”,你可以看到這些字符對應的ASCII碼。你就知道原來隱藏的其實是製表Tab鍵和雙引號。

圖4 – 用站長工具準確確認

什麼是非打印字符？

非打印字符指在計算機中有一些字符是確確實實存在,但是它們不能夠顯示或者打印出來。以ASCII碼錶為例,ASCII碼值在0-31的為控制字符,無法顯示和打印，比如回車鍵。

知道了根本原因之後，我們解決起來就可以對症下藥，解決的辦法有三種：

1. 用Excel自帶的CLEAN函數

函數語法：CLEAN(text)參數說明：text，表示要刪除非打印字符的文本。功能說明：刪除文本中所有不能打印的字符。 對從其他應用程序導入的文本使用 CLEAN，將刪除其中含有的當前操作系統無法打印的字符。 例如，可以使用 CLEAN 刪除某些通常出現在數據文件開頭和結尾處且無法打印的低級計算機代碼。

圖5 – 用CLEAN()函數清洗

處理之後，再複製到文本文檔里，你就發現空格和雙引號都消失了。

圖6 – 用CLEAN()函數清洗效果確認

2. 說到清洗數據，我們自然也要想到Excel里強大的數據清洗插件POWER Query，用Power Query的“修整”和“清除”，這兩種方法也都可以實現。為了方便顯示，我們用添加列選項卡里的”格式”來做個對比。

• 選中數據列，分別點格式里的修整和清除。

圖7 – 用POWER Query清洗

如下圖顯示：

圖8 – 用POWER Query的修整和清除功能清洗

• 然後點“關閉並上載”

圖9 – 清洗後關閉並加載

加載到清洗後的表格如下：

圖10 – 用POWER QUERY清洗後的數據

• 然後我們再複製到文本文檔里確認，成功清除。

圖11 – 用POWER QUERY清洗後效果確認

作業成功交付。

同事J : “大神，請接受我的膜拜！三種方式都可行。感謝解惑，學習了。”

解決了困擾他們幾年的問題，從此在同事J的部門樹立江湖地位，哈哈。