One More Column Problem/Thank you Stefi, Pete and Dave!

T

Templar

Thank you Stefi, Pete and Dave. Things worked out well. I ran into one more
column problem. I need to separate three pieces of information into separate
columns, that is city, state, zip. Here is what my problem looks like.


OAK PARK, IL 60302
OAK PARK, IL 603020000
OAK PARK, IL 60302
OAK PARK, IL 60302
OAK PARK, IL 603024272
OAK PARK, IL 60302
OAK PARK, IL 603022609
OAK PARK, IL 603024272
OAK PARK, IL 60304
OAK PARK, IL 603020000
OAK PARK, IL 60302
OAK PARK, IL 60304
OAK PARK, IL
OAK PARK, IL 60304
OAK PARK, IL 60302
OAK PARK, IL 60302
OAK PARK, IL 603020000
OAK PARK, IL 60302
OAK PARK, IL
OAK PARK, IL 60304
OAK PARK, IL 60302
OAK PARK, IL 60304
OAK PARK, IL 60302
OAK PARK, IL 60302
OAK PARK, IL
OAK PARK, IL 60302
OAK PARK, IL 60304
 
S

Sean Timmons

Highlight your column, go to:

Data - Text to Columns - Delimited - (space). You can also set , as your
delimiter and treat consecutive delimiters as one to get rid of the comma.
 
P

Pete_UK

You can use a similar formula to those given to separate out the first
part (treat it as your last name), as you have a comma that determines
where to split the data, and you can get the state and zip into
another column (if you treat it as your first name). If you then fix
the values of the state/zip column, you could use Data | Text-to-
columns to split the data using space as the delimiter. Alternatively,
the formulae which were looking at your address and splitting on the
first space could be applied, but you might have unexpected results
where you do not have a zip code (though you can test for this).

Hope this helps.

Pete
 
S

Sean Timmons

Boo me..

In B2:
=LEFT(A2,FIND(",",A2)-1)
in C2:
=MID(A2,FIND(",",A2)+2,2)
in D2:
=RIGHT(A2,LEN(A2)-FIND(" ",A2,FIND(",",A2)+2))
 
P

Pete_UK

Sean has done so just before you posted - he assumes your data is in
column A, so you will need to change the references in the formulae to
suit your layout.

Pete
 
Top