在Excel中的表格,如果儲存格中輸入了含文字及數字的字串,想要將其分離來應用,該如何處理呢?

假設資料輸入在A欄,資料的最大長度為20個字。

(1) 前半部

儲存格B2:{=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),
ROW(INDIRECT("1:20")),99))-1)}

此公式為陣列公式,輸入要按 Ctrl + Alt + Enter。

複製儲存格B2到儲存格B2:B12。

原理解析:

ROW(INDIRECT("1:20"))

會產生陣列值:{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}

MID(A2,ROW(INDIRECT("1:20")),1)

將儲存格A2的內容從第1個字到第20個字,逐一取出。

ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1)

因為MID函數取出的任一個字都視為「文字」,所以將其乘以1,如果是文字則結果為#VALUE,如果是數字則結果為該數字。再利用ISNUMBER函數測試1~20的位置所取出的字是否為數字(True, False)。

IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),
ROW(INDIRECT("1:20")),99))

判斷1~20的位置如果為數字,則會藉由ROW(INDIRECT("1:20"),給予對應的數字(1~20),否則給予99,例如「OK232」會得到「{99,99,3,4,5}」(表示第1,2個字為文字,第3,4,5個字為數字。)

=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),
ROW(INDIRECT("1:20")),99))-1)

利用LEFT函數,由左邊取出第n個數字開始的位置再-1個數,即是字串的前半部。

 

(2) 後半部

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

複製儲存格C2到儲存格C2:C12。

和用RIGHT和LEN函數,取出字串的後半部。

 

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

arrow
arrow
    全站熱搜

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