在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函數,取出字串的後半部。
留言列表