tft每日頭條

 > 科技

 > excel怎麼去除隐藏的字符

excel怎麼去除隐藏的字符

科技 更新时间:2025-01-08 06:38:39

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)1

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

先說下為什麼會出現不可見字符:

大多是從某些系統裡下載導出的Excel表格,部分日期或者數據,由于編碼格式等問題,産生了不可見字符或者空格。

⭐通常出現在字符串的首尾。

導緻的後果有vlookup無法正确匹配,函數公式或者計算操作無法正常進行等等。

「去除異常字符是我們進行數據清洗中的重要一環」

1.空格去除

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

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)2

替換法會将數據中所有空格全部替換為空,trim函數會至少保留字符之間的一個空格,并去除左右空格。

替換法選中需要替換的數據區域,按CTRL H打開替換窗口,查找值輸入空格,替換值不輸入,全部替換,則去除數據中的所有空格。

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)3

替換效果

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)4

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

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)5

2.去除不可見字符

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

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

如果你覺得表格中存在非打印字符,可以複制表格數據粘貼到TXT記事本中,如果出現其他字符和空格,則代表存在非打印字符。

Excel中去除方法很容易,使用CLEAN函數直接去除即可。

使用方法與上文的Trim函數一緻。

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

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)6

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

2.1 定位不可見字符

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

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)7

雙擊單元格,進入數據編輯界面,此時看到閃動的光标。按鍵盤上的右方向鍵→,依次向右移動光标。

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

由于不可見字符通常難以用鼠标選取,則可以通過函數LEFT、mid、right函數來直接提取。

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

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

「通過數組公式來拆分字符串」

數組公式如下:

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

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)8

數組公式使用方法

  1. 需提前選中B1:M1區域,因為需要承接拆分的字符,可以盡可能大一點。
  2. 再輸入數組公式
  3. 最後需要按數組确認鍵CTRL SHIFT 回車 确認公式

通過數組公式直接拆分字符,可以精确看到空白字符的位置,接下來,複制字符去替換即可。

3.用SUBSTITUTE函數替換

使用CTRL H替換非常快捷,但是如果數據是身份證号碼或長度大于11位的數字,一旦去除不可見字符,可能會導緻格式直接變成科學計數,導緻數據丢失。

因此,可以使用函數來實現精準替換。

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)9

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

或者也可以把不可見字符複制粘貼到記事本再複制回來,直接寫在公式裡,記得加""号。

但是上面的公式并未成功替換不可見字符,準确來說,隻替換了一個,還剩結尾1個。

幹脆點,就直接再嵌套1個substitute函數,此時結果如下。

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

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)10

substitute函數返回結果默認文本,不用擔心格式變化。

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

excel怎麼去除隐藏的字符(去除Excel中的不可見字符)11

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

總結一下

⭐清洗Excel數據中不可見字符的主要邏輯:

  1. 定位不可見字符,複制它
  2. CTRL H或者函數substitute替換

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

  1. Trim函數去除多餘空格
  2. Clean函數去除非打印字符

以及檢測字符數量和截取字符的函數:

  1. Len函數返回字符個數
  2. Left函數從左側截取字符
  3. Mid函數從中間截取字符
  4. Right函數從右側截取字符

看到這個了嘛,求一個

,

更多精彩资讯请关注tft每日頭條,我们将持续为您更新最新资讯!

查看全部

相关科技资讯推荐

热门科技资讯推荐

网友关注

Copyright 2023-2025 - www.tftnews.com All Rights Reserved