Parsing Letters and Numbers (And Letters)

With this list in A1:A4
SEANHUFFINGTON1968CANADA
ALEXAVALLANCE717ENGLAND
EDWARDBERTRAM9113CANADA
MICHAELSWAIN4683USA

As long as the structure is ALWAYS: textnumberstext
this regular formula begins the list of text prior to numbers in those cells

Please Login or Register  to view this content.

and this regular formula begins the list of text after the numbers in those cells

Please Login or Register  to view this content.

In the above example, these are the returned values:

Please Login or Register  to view this content.

Does that help?

Assuming starting in A1,

in B1:

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

in C1:

=LOOKUP(9.999999999E+307,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$30))))

in D1:

=SUBSTITUTE(A1,B1&C1,"")

each copied down

Note: if you don't want the number column, then in C1:

=SUBSTITUTE(A1,B1&LOOKUP(9.999999999E+307,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0 123456789")),ROW($1:$30)))),"")

Perhaps a blend of our approaches?:

Your name formula (better than the formula I posted)

Please Login or Register  to view this content.

and the location formula I posted:

Please Login or Register  to view this content.

Good idea

EDIT:

Thank you so much, gentlemen. You really made my day!

See my first Post... #3