You can achieve the same with a combination of functions. With your text in
cell A1 try the below formulas.

'in cell B1
=LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123 456789"))-1)

'in cell C1
=MID(TRIM(LEFT(SUBSTITUTE(A1 & " "," SW ",REPT(" ",255)),255)),
LEN(B1)+1,255)

--
Jacob (MVP - Excel)


"Jim Berglund" wrote:

Consider the following entries in Column A1:

CHRISTIAN 32 PATTERSON ME SW
TOM & PAT 235 PROMINENCE HGTS SW CALG
M 26 PATTERSON CL SW CALG
L P 54 PATTERSON PK SW CA
ROBERTO JOSE 27 PATTERSON PL SW CAL

I want to parse this out. Here's here are the columns I want:
B: Left(A1, find the first number)-2) ' Get the first name, first
name plus initial, initial(s), or first and middle names
C: Mid(A1, find the first number, Find("SW", A1) 'Get the Address

Is there any way of writing this so that it works? Is it unreasonable to
want a simple function for doing this, since everyone in business must want
to do this at some point in their lives?

Another useful function would be a BETWEEN function.

=BETWEEN(find the first number,"SW", A1)

Musing...
Jim Berglund




.