excel 點將D英文字同數字分開抽出黎

本帖最後由 ed1 於 2016-1-10 03:27 編輯

example:
A1=abc12345

get number "12345" (ps:result is number format,not text format) in
B1
  1. =LOOKUP(9.9E+307,--MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),ROW(INDIRECT("1:"&LEN(A1)))))
複製代碼
get eng word "abc" in
C1
  1. =SUBSTITUTE(A1,B1,"")
複製代碼

TOP

回覆 2# ed1


    thanks, u saved my life
    How to seperate "hour" n "minute"?
    tnahks again

TOP

本帖最後由 big_nature 於 2016-1-10 14:02 編輯
example:
A1=abc12345

get number "12345" (ps:result is number format,not text format) in
B1get eng w ...
ed1 發表於 2016-1-10 03:15

師兄,
可否講解下公式流程及做法?

TOP

回覆 3# timotimo


    example:
A1=17:33
B1=HOUR(A1)
C1=MINUTE(A1)

TOP

回覆 5# ed1


    sorry, not working

TOP

TOP

死圖...

TOP

timotimo 發表於 2016-1-10 18:34



唔知你E嗰行係用咩format
不如CAP低你E3個FORMAT

TOP

本帖最後由 kkeennken 於 2016-1-11 11:01 編輯

英文前面,數字後面:

sdfdf234234sdfdf234234
seeffs234214seeffs234214
sefewtert234234234sefewtert234234234

sdfdf234234{=LEFT(A2,MIN(IF(ISNUMBER(MID(A2,ROW(INDIRECT("1:20")),1)*1),ROW(INDIRECT("1:20")),99))-1)}=RIGHT(A2,LEN(A2)-LEN(B2))
seeffs234214{=LEFT(A3,MIN(IF(ISNUMBER(MID(A3,ROW(INDIRECT("1:20")),1)*1),ROW(INDIRECT("1:20")),99))-1)}=RIGHT(A3,LEN(A3)-LEN(B3))
sefewtert234234234{=LEFT(A4,MIN(IF(ISNUMBER(MID(A4,ROW(INDIRECT("1:20")),1)*1),ROW(INDIRECT("1:20")),99))-1)}=RIGHT(A4,LEN(A4)-LEN(B4))


作者: shih wenhsien

TOP