贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

創作者介紹

學不完.教不停.用不盡

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


留言列表 (8)

發表留言
  • 試了,但沒用怎麼辦?
  • 我用的是Excel2007
  • 跑跑
  • 應該是shift ctrl enter吧?
  • 謝謝你, 是我打錯字了!陣列公式應按 Ctrl+Shift+Enter 鍵。

    vincent 於 2012/10/10 21:17 回覆

  • 請問如果有一個儲存格文字數字混合,想計算數字加總如何做呢?
  • 例如
    A1=OK231PP2

    我利用以下公式算出來的結果皆為0,請問是哪裡出錯呢?
    {=SUM(IF(ISNUMBER(MID(A1,ROW(INDIRECT("1:20")),1)*1),MID(A1,ROW(INDIRECT("1:20")),1),0))}
  • 現在有IFFERROR函數,使用的公式可以比較短。
    參考這篇:http://isvincent.pixnet.net/blog/post/44490028

    vincent 於 2015/02/02 22:08 回覆

  • 頭痛中
  • 不好意思,想請教一下,我key 入函數後,卻顯示函數過長,無法使用..
    請問怎麼辦呢?
  • 傷腦筋
  • 請問如果字串是 ab123cd45e 這種格式,有辦法分別取出 123 跟 45 嗎?
  • randy
  • 我遇到的問題如下:
    1.英文字可能出現在字串任何位置
    2.英文字母沒有固定位置和字元數
    3.取出的數字要轉成國字,如果數字超過99,則國字不含「十」「百」「千」
    如下舉例:
    14d → 十四D
    A223→ A二二三
    1Ac17B→ 一AC十七B

    目前我是用分段取出放入不同儲存格,再用TEXT、BIG5函數各別轉換,再把所有儲存格資料合併,不知道是否有更簡單的公式在一個儲存格內完成所有計算?
  • 請參考:http://isvincent.pixnet.net/blog/post/46930870

    vincent 於 2016/12/30 10:34 回覆

  • Robert
  • 請教,若是在 a2欄輸入 1或 2或 3時,無動作。但當a2欄輸入4時,則將a2、b2、c2、d2抓取出來同步到a8、b8、c8、d8顯示。請教,該從哪著手呢?謝謝

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼