贊贊小屋

Excel資料處理: RIGHT、LEN、TRIM、MID、FIND、REPLACE文字函數

Excel資料處理常要對付不講道理文字,本文以業務扣款明細為例,介紹利用RIGHT、LEN、TRIM、MID、FIND、REPLACE等文字函數公式組合,順利以客戶集團首碼替代星字號。

會計工作上有個實際案例,收到的文件有一欄是存貨料號,可是這個料號,有一碼被弄成「*」,具體原因不明,對方只是說有另外一欄是客戶代碼,把客戶代碼第一碼替換存貨料號的「*」,便是系統裡的存貨料號了。以下針對這個案例作分享:

一、無言的客戶扣款明細表

如圖所示,收到的報表長這樣,標黃色的,便是被搞了一個星字號的存貨料號。

Excel資料處理: RIGHT、LEN、TRIM、MID、FIND、REPLACE文字函數 文字函數 第1張

二、好用的RIGHT與LEN文字函數

直覺想法,既然是倒數第二碼末兩碼,那就來個「=RIGHT(B2,2)」,取目標字串末兩碼,先把有問題的抓出來再說,可是隨即發現,在「E6」這個儲存格,竟然顯示空白,通常會出現這種靈異現象,是因為目標字串由於不知名原因(有時候是系統導出來就是這樣),末兩碼真的就是空格。我們可以簡單做個實驗,再下個函數公式:「= LEN (B6)」,意思是傳回文字字串中的字元數,結果是「9」,但是「O178E*Q」怎麼數都是只有七位數,這就表示,後面還有兩碼是空格,看不見。

Excel資料處理: RIGHT、LEN、TRIM、MID、FIND、REPLACE文字函數 文字函數 第2張

三、看不見摸得到的暗黑空格

雖然看不見,但是摸得到!把滑鼠點在「B6」儲存格,發現指標停在字串後面兩格,往左移動指標,便可摸到這兩個空格。

Excel資料處理: RIGHT、LEN、TRIM、MID、FIND、REPLACE文字函數 文字函數 第3張

四、TRIM函數專用槓掉空格

確認問題點,那就好辦了,在E欄弄個公式:「=TRIM(B6)」,刪除文字字串中多餘的空格。

Excel資料處理: RIGHT、LEN、TRIM、MID、FIND、REPLACE文字函數 文字函數 第4張

五、文字函數大組合揪出星字號

接下來理所當然:「RIGHT(E6,2)」,有星字號的會出現在第一碼,如圖片上標黃色所示,或者不囉嗦一次到位:「=MID(TRIM(B6),LEN(TRIM(B6))-1,1)」,很漂亮地把星字號都揪出來。

Excel資料處理: RIGHT、LEN、TRIM、MID、FIND、REPLACE文字函數 文字函數 第5張

六、合乎邏輯的FIND和有點煩的REPLACE

講這麼多,純粹是我遇到這個案例的個人解題思路,如果只是要把帶有星字號的儲存格找出來,最直接的公式:「=FIND(“*”,B6)」。然後如果是將星字號以客戶首碼替代,一次到位不囉嗦但是很噁心的公式如下:「=IFERROR(REPLACE(B6,FIND(“”,B6),1,LEFT(A6,1)),B6)」,結果如圖所示。

Excel資料處理: RIGHT、LEN、TRIM、MID、FIND、REPLACE文字函數 文字函數 第6張

七、幫幫忙的函數引數視窗

如果有遇到會計Excel高高手傳來的文件,發現裡面公式寫的劈哩叭啦,可以把滑鼠移動公式列,看是對哪一個函數想要進一步瞭解,只要點一下公式列左邊的「fx」便會跳出函數說明的視窗,非常便於理解,有興趣點看看吧!

Excel資料處理: RIGHT、LEN、TRIM、MID、FIND、REPLACE文字函數 文字函數 第7張

文字函數經典組合拳

這一節主要利用範例介紹Excel如何處理儲存格內容,諸如RIGHT 、LEFT 、TRIM、MID、FIND、REPLACE等,都是會計工作可能會用到的經典函數,實務上如果遇到困難,可以跑到上方功能區的「公式」,在「函數程式庫」裡有「文字」類函數清單,也許能找到適合自己需要的。