separating numeric and alpha cell contents

[lintcoop]

Hello,

I have a set of text strings that have some number of alpha characters followed by some number of numeric characters. I need to separate the data into two cells.

Example in A1-A5

ABC123456
AB12345
A123456
AA12345
DEF123456

Desired results in B1 - C5

ABC 123456
AB 12345
A 123456
AA 12345
DEF 123456

Can anyone help?

[Domenic]

Assuming the format is consistent, try...

B1, copied down:

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

C1, copied down:

=SUBSTITUTE(A1,B1,"")

Hope this helps!

[lintcoop]

That worked Domenic! Thanks so much.
I love this forum!

[Domenic]

You're very welcome! Thanks for the feedback!

[bax_man]

Domenic - better than 4 years later, this bit of code helped me too. Now I'm prompted to study those two functions.

Thanks,

bax_man

[Teethless mama]

Another one...but shorter

C1: =LOOKUP(1E99,--RIGHT(A1,ROW($1:$99)))

B1: =SUBSTITUTE(A1,C1,)