網友想要在 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)式。

 

【延伸閱讀】

Excel-分割字串和串接字串(TEXTSPLIT,TEXTJOIN)

Excel-利用勾選以組織評語字串(TEXTJOIN)

Excel-利用TEXTJOIN函數產生Google地圖多地點規劃路線

Excel-產生GUID全域唯一識別碼(TEXTJOIN,RANDBETWEEN)

Excel-拆解字串變直書文字

Excel-利用SEQUENCE函數產生連續數字和讓字串反轉

Excel-將儲存格中的字串倒轉

Excel-產生亂數字串

Excel-固定間隔字元插入符號

Excel-各種字串分割(TEXTSPLIT,TEXTJOIN,TEXTBEFORE,TEXTAFTER)

Excel-將篩選後的清單串接在一個儲存格

Excel-將儲存格內容依分隔符號分成多欄或多列

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    EXCEL MID ROW
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(2) 人氣()