Excel進階文字篩選:TRIM及CLEAN函數清除隱藏的空白

Excel篩選資料是超好用超方便的工具,但實際上可能沒那麼簡單。本文以取得三碼字串為例,介紹問號萬用字元的用法,進而說明如何以文字函數資料清洗。

一、原始待處理資料

範例資料經過簡化後有兩碼或者三碼的情形,實務上需求是希望篩選出只有三碼的資料出來。注意到這裡以儲存格B11為例,明明是單純三碼,可是在公式編輯列裡卻空空如也,稍後會看到它將影響到Excel後續的操作。

Excel進階文字篩選:TRIM及CLEAN函數清除隱藏的空白 1

二、文字篩選資料

先選取資料範圍之後,上方功能區前往「資料」頁籤,在「排序與篩選」這裡點選「篩選」,接著將B1儲存格會出現的篩選器圖標下拉,選擇「文字篩選」中的「等於」。

Excel進階文字篩選:TRIM及CLEAN函數清除隱藏的空白 3

三、萬用字元篩選

在跳出來的「自訂自動篩選」視窗這裡,輸入三個問號「???」,這裡是使用到了萬用字元,在視窗左下角便有相關的文字說明可供參考:「可使用?代表任何單一字元」,因此三個問號「???」表示想取得有三個字元長度並且內容不拘的資料。

Excel進階文字篩選:TRIM及CLEAN函數清除隱藏的空白 5

四、篩選資料有誤

實際篩選結果發現到是完全的空空如也,沒有任何資料符合狀況,因此中間有資料的範圍,第2列到第11列都被隱藏起來了,在列號那邊直接從1跳到12。

Excel進階文字篩選:TRIM及CLEAN函數清除隱藏的空白 7

五、清除及取消篩選

既然篩選失敗,必須重新再來。可以在篩選器下拉選單中擇選「清除”資料”中的篩選」,它的作用是取消三個問號的篩選狀態,同時保留篩選器,變成是待輸入篩選條件、無任何篩選的情況。

也可以和第二步驟同樣前往「排序與篩選」指令區塊,這裡的「清除」和前述「清除”資料”中的篩選」是相同作用,或者直接點選「篩選」指令,如此會完全移除篩選器。

由於在此並不是篩選條件設定的問題,而是原始資料必須再經過處理,所以直接將篩選器完全移除,等於砍掉重煉。

Excel進階文字篩選:TRIM及CLEAN函數清除隱藏的空白 9

六、TRIM及CLEAN函數

先前第一步驟有提到其實從公式編輯列可以看出有些奇怪的地方,儲存格內應該包含了其他不必要的多餘資料,為了確認狀況,可以先用LEN函數,它的作用是傳回文字字串的字元個數,從截圖中的「函數1」的計算結果是「5」或者「6」,顯然有問題。

接著像這種情況可以使用兩個函數,其中CLEAN函數的作用是移除文字中無法列印的字元,這裡通常是因為程式進行資料處理時可以會加入一些看不到的東西所產生的;另外一個TRIM函數則是會去除掉文字字串兩邊多餘的空格。將這兩個函數合起來便可以達到清洗資料的目的,亦即截圖中的「函數2」公式:「=TRIM(CLEAN(B2))」。

最後再以LEN函數驗證,截圖中的「函數3」傳回結果是「2」或「3」,表示確實達到資料清洗的目的了。

Excel進階文字篩選:TRIM及CLEAN函數清除隱藏的空白 11

七、特殊三碼篩選

資料清洗後,再用先前第三步驟同樣方式進行三個問號篩選,這次果然非常成功篩選出只有三個字碼的資料出來。

Excel進階文字篩選:TRIM及CLEAN函數清除隱藏的空白 13

Excel篩選及函數實戰

這篇文章的範例資料很簡單,需求也是很簡單,然而在實際執行過程中,有必要瞭解基本篩選操作及萬用字元的進階篩選功能,還要用到文字函數進行資料清洗,這正是在實務工作上Excel的妙用所在,提供給各位讀者分享作為參考。

加強學習:贊贊小屋Excel函數文章