粉絲群裡有同學遇到了表格中不可見字元問題,今天一篇文章教你各種去除不可見字元方法。
先說下為什麼會出現不可見字元:
大多是從某些系統裡下載匯出的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)
陣列公式使用方法
需提前選中B1:M1區域,因為需要承接拆分的字元,可以儘可能大一點。
再輸入陣列公式
最後需要按陣列確認鍵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資料中不可見字元的主要邏輯:
定位不可見字元,複製它
CTRL+H或者函式substitute替換
還有兩個專門清洗空格和非列印字元的函式。
Trim函式去除多餘空格
Clean函式去除非列印字元
以及檢測字元數量和擷取字元的函式:
Len函式返回字元個數
Left函式從左側擷取字元
Mid函式從中間擷取字元
Right函式從右側擷取字元
看到這個了嘛,求一個