2010年6月26日 星期六

Excel-分離儲存格中的文字和數字

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

1 則留言:

  1. 你好 ~
    我想請問如果我A1存儲格內是一個地址,我想把它分割成3-4個存儲格(每個字數限制是30)放到A2,A3,A4 ...
    而又不會打斷地址中的字詞(building不會俾打斷成bui跟lding), 請問這個可以做到嗎?? 謝謝 ~

    回覆刪除

檢視其他文章

好康東東