Excel的大魔王：不可視字元(NO-BREAK SPACE, )

公司有許多同事Excel功力已臻化境，Vookup、樞紐分析等功能非常上手。但有時會遇到一個棘手的問題，某些客戶編號(學號)會對不到，或是排序時，明明同屬於一組單位代碼109的資料，卻被排序到不同的位置去。


一般而言，這是因為要拿來比對的兩個欄位之間 (1)資料型態不同 (2)比對的字串末尾夾雜著不可視字元。型態不同的問題，可以利用Excel的「資料剖析」將欄位裡的資料整欄選取，使其變成真正的數字或文字(光改儲存格格式是沒有用的)。但如果資料來自於網站複製貼上的文字，在每個欄位末尾可能藏著設計師放進去、美化表格的字元，肉眼保證看不到，這就超悲哀了。


通常可用的方法有： 1.用len()函數查一下字串的長度，例如儲存格 A1的值是109， len(A1)卻看到長度是4；不得了，後面有鬼。


2.如果已知資料長度固定，那麼資料剖析時選固定寬度，可以像切西瓜一樣從你要的長度位置切下去，後面的就不要了。


3.如果長度不固定而且數字文字混雜，，可以先用 clean()、trim()等函數清一次；如果不能奏效，只能用SUBSTITUTE這個函數將不要的可疑字元濾除。


以這次的案例而言，本來以為是夾雜空白字元，乾脆發狠利用Excel內的char()函數，從1-255組出ASCII全部的字元做比對。結果還是找不到(崩潰)？


後來想想，這個已知的不可見字元在字串末尾，用right(xx,1)可以找出來他的本尊，再用code()函數可以找出他的字碼。得出的結果是char(63)，是個問號? 這個答案是錯的。最後看一下Excel還有個函數就叫unicode，可以依照字元查編碼，得出這個搗蛋鬼的真正樣貌是 UNICHAR(160)，學名是不換行空格(NO-BREAK SPACE)，人果然是他殺的。要取她的字元，則要用 UNICHAR(160)。


https://zh.wikipedia.org/wiki/%E4%B8%8D%E6%8D%A2%E8%A1%8C%E7%A9%BA%E6%A0%BC


由此，網路上流傳的特殊字元清除名單，在Excel中處理時應該略作調整。


Char(1)，空字符NULL

Char(9)，tab

Char(10)，換行

Char(13)，Enter

Char(32)，空格

Char(160) 找不到的話 => unichar(160)


最後用 SUBSTITUTE(C4,UNICHAR(160),"") 將其取代，終於打完收工。