Thread: Extract Data to right and left of numbers

Friends,

I need a bit of help as to how to extract data from the right and left of numbers in address cells.

One cell contains the following:

MIKE A 335 W FIRST ST - I want to extract the MIKE A and 335 W FIRST ST

One cell contains the following with no spaces between text and number:

JENNIFER MARIE837 W SECOND AVE - I want to extract the JENNIFER MARIE and 837 W SECOND AVE

Your assistance will be greatly appreciated.

The following are array formulas - confirm with Crtl+Shift+Enter
For the text before the first number.

=LEFT(A1,MATCH(1,(CODE(MID(A1,ROW(1:100),1))<=57)*(CODE(MID(A1,ROW(1:100),1))>=48),0)-1)

For the text starting at the nubmer:

=MID(A1,MATCH(1,(CODE(MID(A1,ROW(1:100),1))<=57)*(CODE(MID(A1,ROW(1:100),1))>=48),0),99)