Extracting text and numbers

Hi,

I have a cell from a database which doesn’t split out the text and numbers into separate columns so I would like to do this manually.

I have the following codes to split certain elements out:

Text

=LEFT(B15,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B15&"0123456789"))-1)

Number

=LOOKUP(10^99,--MID(B15,MIN(IF((--ISNUMBER(--MID(B15,ROW($1:$25),1))=0)*ISNUMBER(--MID(B15,ROW($2:$26),1)),ROW($2:$26))),ROW($1:$25)))

(CSE Formula)

The Problem I have is that it doesn’t deal with the fact it’s a negative number, is there a workaround?

Hi,

With sample data this seems to work,

Extract number first with below formula in E16

=LOOKUP(9.9999999E+307,--RIGHT(B15,ROW(INDIRECT("1:"&LEN(B15)))))

Than text with below formula
=TRIM(SUBSTITUTE(B15,E16,""))

Regards,

Another Try but not clever solution

Regards

Thanks all - all codes supplied worked!