excel 擷取需要的數字

想請問

05/20/2013 23:55:00 − 00:00:00 183 MByte 6 Mbit/s 1,165 MByte 37 Mbit/s 87 %

05/20/2013 23:40:00 − 23:45:00 200 MByte 6 Mbit/s 1,276 MByte 36 Mbit/s 100 %

05/20/2013 19:15:00 − 19:20:00 250 MByte 7 Mbit/s 949 MByte 27 Mbit/s 100 %

05/20/2013 13:40:00 − 13:45:00 127 MByte 4 Mbit/s 964 MByte 27 Mbit/s 100 %

05/20/2013 03:10:00 − 03:15:00 234 MByte 7 Mbit/s 168 MByte 5 Mbit/s 100 %

05/20/2013 00:45:00 − 00:50:00 247 MByte 7 Mbit/s 1,298 MByte 36 Mbit/s 100 %

若找想擷取字串中第二個"MByte"之後的數字,

即字串中的37,36,27,27,5,36

應該要如何寫公式,

麻煩各位大大幫我解答,

謝謝!

Update:

謝謝 T_vasus ,

我想再請問,

若資料中間的數字為三位數

例:05/20/2013 16:10:00 − 16:15:00 214 MByte 6 Mbit/s 3,951 MByte 110 Mbit/s 100 %

要擷取110

我把您的公式最後的2改為3後是可以的

=MID(A1,FIND("MByte",A1,FIND("Mbit/s",A1))+6,3)

但資料為個位數的數字會多擷取出"M"

因原資料要擷取的數字位數不一定,

請問公式要如何做修改呢,

麻煩了,謝謝!

Update 2:

謝謝各位大大的熱心回答,

我本來也是有想到要用分割的方式來做,

但因其中還牽涉到很多問題,

必需用公式在原工作表中完成,

所以還是希望能找到能解決的公式

Update 3:

再次謝謝各位大大的解答,

因KK大師的公式比較符合我的需求,

解出來的答案直接為數值,

所以麻煩KK大師幫我將意見移至回答區,

另,想請問

=-LOOKUP(,-MID(SUBSTITUTE(A1,"MByte",REPT(" ",99),2),99,ROW($1:$99)))

希望之後也能活用公式,所以想請問,

公式內 「,-MID」的,號、「",REPT(" ",99),2),"」和ROW內的1:99

這個各代表什麼意思,

請大師幫我解答,

謝謝!

6 Answers

Rating
  • KK
    Lv 7
    7 years ago
    Best Answer

    =-LOOKUP(,-MID(SUBSTITUTE(A1,"MByte",REPT(" ",99),2),99,ROW(1:99)))

    2013-05-31 22:43:18 補充:

    修改一下

    =-LOOKUP(,-MID(SUBSTITUTE(A1,"MByte",REPT(" ",99),2),99,ROW($1:$99)))

    2013-06-02 01:00:11 補充:

    =-LOOKUP(,-MID(SUBSTITUTE(A1,"MByte",REPT(" ",99),2),99,ROW($1:$99)))

    1. SUBSTITUTE(A1,"MByte",REPT(" ",99),2),是將第二個 "MByte" 用 99個空格取代,字串被空白相隔2段。

    例如:05/20/2013 23:55:00 − 00:00:00 183 MByte 6 Mbit/s 1,165 ...(99個空白)... 37 Mbit/s 87 %

    2.-MID(SUBSTITUTE(A1,"MByte",REPT(" ",99),2),99,ROW($1:$99))

    表示由99個位置開始取1~99(ROW($1:$99))個字,取完加上-將轉為負數字,非數字會產生錯誤。

    例如:

    取出文字如下

    " " ; " ";...." 3"; " 37"; " 37 M"; ....

    -MID(表示將文字轉為負數

    #VALUE!;#VALUE!;...;-3;-37;#VALUE!;#VALUE!...

    用-LOOKUP(, 表示LOOKUP用0查詢,因數值是負數,會找到最後一數 -37

    ,最後-LOOKUP前之負號再變號一次,得37

    你可以在公式編輯區反白部分公式,按F9看看計算過程。

  • 鬍鬚
    Lv 7
    7 years ago

    B1=TRIM(LEFT(SUBSTITUTE(RIGHT(SUBSTITUTE(A1,"MByte",REPT(" ",99)),99),"Mbit/s",REPT(" ",99)),99))

    下拉

  • 7 years ago

    C1(輔助)=FIND("Mbit/s",A1)+6

    D1=MID(MID(A1,C1,99),FIND("MByte",MID(A1,C1,99))+5,FIND("Mbit/s",MID(A1,C1,99))-FIND("MByte",MID(A1,C1,99))-5)

    下拉 A1:A6資料

  • 7 years ago

    其實你在匯入資料時, 要勾選空格, 這樣數字會跟文字分開, 就不用做那麼多了

  • How do you think about the answers? You can sign in to vote the answer.
  • 7 years ago

    會不會太複雜化了,建議是否可簡單化單一處理.

    你的資料應是外部資料不管如何應可儲存為文字檔例如201305DATA.TXT

    然後開啟舊檔>選文字檔案>選取201305DATA.TXT檔案>確定.

    點選分格符號>下一步>點選空格>點選連續分格符號視為單一處理>其他取消>完成.

    結果:H欄就是你要的答案.

    2013-05-31 16:26:18 補充:

    如此一來不管其他數值是幾位數字公式都不必修改直接套用再將H欄數值取出即可.以上是個人愚見供其參考.

    PS:注意此時非excel檔而是文字檔儲存時需注意或者此檔是緩衝(交換)檔根本沒有儲存之必要步是嗎?

    Source(s): Me~, Me~
  • 7 years ago

    假設資料在A1

    B1公式

    =MID(A1,FIND("MByte",A1,FIND("Mbit/s",A1))+6,2)

    2013-05-31 18:33:35 補充:

    這樣公式會變長

    5位數也沒問題

    =MID(A1,FIND("MByte",A1,FIND("Mbit/s",A1))+6, (FIND("Mbit/s",A1,FIND("Mbit/s",A1)+10)-1)- (FIND("MByte",A1,FIND("Mbit/s",A1)+10)+6))

    其他的用複製

Still have questions? Get your answers by asking now.