How to separate text and numbers from an alphanumeric data. e.g.,
3489djffdj3489kdf 34893489 djffdjkdf
Hi,
Both of these are ARRAY formula, see below for how to enter them.
For the numbers.
=MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($A$1:$A$300)-1)*ISNUMBER(-MID("01"&A1,ROW($A$1:$A$300),1)),ROW($A$1:$A$300))+1,1),10^(300-ROW($A$1:$A$300))),2,300)
And a bit long but for the letters.
=SUBSTITUTE(SUBSTITUTE(A1,LOOKUP(1E+100,--MID(MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")),MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),""))),SMALL(FIND({0,1,2,3,4,5,6,7,8,9},MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")),MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")))&"0123456789"),1),ROW(INDIRECT("1:"&LEN(MID(A1,MIN(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")),MAX(IFERROR(SEARCH(CHAR(ROW($65:$90)),A1),"")))))))),""),LOOKUP(1E+100,--MID(A1,SMALL(FIND({0,1,2,3,4,5,6,7,8,9},$A$1&"0123456789"),1),ROW(INDIRECT("1:"&LEN($A$1))))),"")
This is an array formula which must be entered by pressing CTRL+Shift+Enter
and not just Enter. If you do it correctly then Excel will put curly brackets
around the formula {}. You can't type these yourself. If you edit the formula
you must enter it again with CTRL+Shift+Enter.