Spliting Cell Contents

[Nick]

Hello, I am working on Excel v.X for Mac. I have a list of names and
addresses and the addresses are all in one cell. Is there a formula to help
separate the street from the city and house? A typical listing looks like this:
293 ELLIS AVE NICEVILLE FL32838

 

[Peo Sjoblom]

One way assuming that there is a number, street name and type of street

=LEFT(TRIM(A1),FIND("^^",SUBSTITUTE(TRIM(A1),"
","^^",3)))=LEFT(TRIM(A1),FIND("^^",SUBSTITUTE(TRIM(A1)," ","^^",3)))

will return

293 ELLIS AVE

then to get the rest just substitute the extracted values from the original,
assume you have the above in B1, in C1 use

=SUBSTITUTE(TRIM(A1),B1,"")


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


Hello, I am working on Excel v.X for Mac. I have a list of names and
addresses and the addresses are all in one cell. Is there a formula to help
separate the street from the city and house? A typical listing looks like
this:
293 ELLIS AVE NICEVILLE
FL32838

 

[Peo Sjoblom]

Oops! You can also select the column and do data>text to columns, in this
case fixed delimiter, click next, double click to remove some delimiters and
click finish


--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)


Hello, I am working on Excel v.X for Mac. I have a list of names and
addresses and the addresses are all in one cell. Is there a formula to help
separate the street from the city and house? A typical listing looks like
this:
293 ELLIS AVE NICEVILLE
FL32838