2017年3月30日 星期四

Excel-將儲存格中的字串分別取出中文字和英文字(MID,ROW,陣列公式)

網友想要在 Excel 的工作表中將含有中英文的字串,分別取出中文字和英文字,該如何處理?
在下圖中可見,可能一個儲存格的中文字和英文字是分列左右兩側,也可能是中文字插於英文字之間或是英文字插於中文字之間。以下分別來討論。
Excel-將儲存格中的字串分別取出中文字和英文字(MID,ROW,陣列公式)

1. 儲存格中串字是中文字和英文字分列左右兩側
Excel-將儲存格中的字串分別取出中文字和英文字(MID,ROW,陣列公式)
【公式設計與解析】
(1) 取出左側中文字
儲存格B2:=LEFT(A2,LENB(A2)-LEN(A2))
LEN 函數會傳回文字字串中的字元數(中英文都算1個字元)。
LENB 函數會傳回文字字串中字元的位元組數(中文字=2位元組/英文字=1位元組)。
(2) 取出右側英文字
儲存格C2:=RIGHT(A2,2*LEN(A2)-LENB(A2))
(3) 取出右側中文字
儲存格B4:=RIGHT(A4,LENB(A4)-LEN(A4))
(4) 取出左側英文字
儲存格C4:=LEFT(A4,2*LEN(A4)-LENB(A4))

2. 儲存格中字串是中文字插於英文字之間或是英文字插於中文字之間
Excel-將儲存格中的字串分別取出中文字和英文字(MID,ROW,陣列公式)
【公式設計與解析】
為了解說方便,使用輔助欄位。(E欄和F欄)
先來處理取出英文字。
(1) 找出儲存格A2字串第1個英文字的位置(儲存格E2)
公式:{=MIN(IF(BIG5(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<>MID(A2,
ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2))),""))}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
(1.1) ROW(INDIRECT("1:"&LEN(A2)))
因為儲存格A2內容為「一二三ABCD四五六」,
所以 LEN(A2)=10,因此 INDIRECT("1:"&LEN(A2))=INDIRECT("1:10")
在此利用 INDIRECT 函數將字串轉換為位址。
ROW(INDIRECT("1:"&LEN(A2)))=ROW(1:10)={1,2,3,4,5,6,7,8,9,10}
ROW 函數會傳回儲存格的列號。
(1.2) MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)
在陣列公式中利用 MID 函數將儲存格A2中的每一個字元逐一找出來,結果:
={"一", "二", "三", "A", "B", "C", "D", "四", "五", "六"}
(1.3) BIG5(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))
利用 BIG5 函數將其中每個字元都轉成全型字,結果:
={"一", "二", "三", "A", "B", "C", "D", "四", "五", "六"}
(1.4) IF((1.3)式<>(1.2)式,(1.1)式,"")
判斷若全型字元和原來字元不相等者,代表是英文字,則傳回該字元的位置(數字),否則,傳回空字串。本例傳回:{"";"";"";4;5;6;7;"";"";""}
(1.5) MIN((1.4)式)
公式=MIN({"";"";"";4;5;6;7;"";"";""})=4。

(2) 找出儲存格A2字串最後1個英文字的位置(儲存格F2)
公式:{=MAX(IF(BIG5(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1))<>MID(A2,
ROW(INDIRECT("1:"&LEN(A2))),1),ROW(INDIRECT("1:"&LEN(A2))),""))}
第(2)式和第(1)式的差別有於將 MIN 函數改成 MAX 函數,因此
公式=MAX({"";"";"";4;5;6;7;"";"";""})=7。

(3) 找出儲存格A2字串中的英文字(儲存格C2)
公式:=MID(A2,E2,F2-E2+1)=MID("一二三ABCD四五六",4,7-4+1)="ABCD"

接著來處理取出中文字。
(4) 找出儲存格A4字串第1個中文字的位置(儲存格E4)
公式:{=MIN(IF(BIG5(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1))=MID(A4,
ROW(INDIRECT("1:"&LEN(A4))),1),ROW(INDIRECT("1:"&LEN(A4))),""))}
原理同第(1)式,但是將「<>」改成「=」。
公式=MIN({"";"";"";"";5;6;7;8;"";"";"";"";""})=5

(5) 找出儲存格A4字串最後1個中文字的位置(儲存格F4)
公式:{=MAX(IF(BIG5(MID(A4,ROW(INDIRECT("1:"&LEN(A4))),1))=MID(A4,
ROW(INDIRECT("1:"&LEN(A4))),1),ROW(INDIRECT("1:"&LEN(A4))),""))}
原理同第(2)式,但是將「<>」改成「=」。
公式=MAX({"";"";"";"";5;6;7;8;"";"";"";"";""})=8

(6) 找出儲存格A4字串中的中文字(儲存格B4)
公式:=MID(A4,E4,F4-E4+1)
原理同第(3)式。

沒有留言:

張貼留言

檢視其他文章

好康東東