2011年10月12日 星期三

Excel-取出字串中的中文字

有網友問到:以下表中的範例,每個儲存格的內容是前後二串中文字夾一串英文字,試著要取出前後的中文字,並比較二者是否相同。

這個題目有一點難,如果不使用VBA(大多數人不喜歡寫程式),則要運用陣列公式才能達成。

首先,參考下表可以得知如果利用 BIG5 函數,不論輸入的是半型字或全型字均會轉成全型字,即半型的英文A會轉成全型的英文A。這個題目要用到這個函數。

為了避免公式冗長,所以分數個儲存格來完成:

(1) 找出英文字在字串中的起迄位置

儲存格B2:{=MIN(IF(BIG5(MID(A2,ROW($1:$20),1))<>MID(A2,ROW($1:$20),1),ROW($1:$20),"X"))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

MID(A2,ROW($1:$20),1):將儲存格A2的內容轉成一個字一個字組成的陣列。

其中的20是設定儲存格A2最長為20個字元,例如:「一二三ABC一二三四」會轉成{"一","二","三","A","B","C","一","二","三","四","","","","","","","","","",""}

BIG5(MID(A2,ROW($1:$20),1))<>MID(A2,ROW($1:$20):如果式子成立(左右不相等),表示該字是半型字(ASCII)。

IF(BIG5(MID(A2,ROW($1:$20),1))<>MID(A2,ROW($1:$20),1),ROW($1:$20),"X")

將 {"一","二","三","A","B","C","一","二","三","四","","","","","","","","","",""} 陣列轉換成{"X","X","X",4,5,6,"X","X","X","X","X","X","X","X","X","X","X","X","X","X"} 陣列。

利用MIN函數取出大於0的最小值:4。

同理:

儲存格C2:{=MAX(IF(BIG5(MID(A2,ROW($1:$20),1))<>MID(A2,ROW($1:$20),1),ROW($1:$20),"X"))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

利用MAX函數取出大於0的最大值:6。

(2) 取出前半部和後半部中文字

儲存格D2:=LEFT(A2,B2-1)

利用LEFT函數,由字串左側取出第1個英字左邊的所有字元。

儲存格E2:=RIGHT(A2,LEN(A2)-C2)

利用RIGHT函數,由字串右側取出最後1個英字右邊的所有字元。

(3) 比較前後兩個中文字是否相同

儲存格F2:=IF(D2=E2,"V","X")

複製儲存格B2:F2,往下各列貼上。

1 則留言:

  1. 您好,請問在excel表中,一儲存格的字串中有中文(長度不等)及數字,我能不能只擷取數字出來呢?

    不知您是否有時間可以幫我這個忙,解決這個難題呢?


    期待您的回復
    ivy chang(335911@gmail.com)

    回覆刪除

好康東東