學不完．教不停．用不盡

在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函數，取出字串的後半部。