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

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

--
Templar

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

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.

"Templar" wrote:

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

--
Templar

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

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

On Oct 2, 4:57*pm, Templar wrote:
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

--
Templar

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

That would put OAK in one column and PARK in another.

Pete

On Oct 2, 5:04*pm, Sean Timmons
wrote:
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..

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

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))

"Pete_UK" wrote:

That would put OAK in one column and PARK in another.

Pete

On Oct 2, 5:04 pm, Sean Timmons
wrote:
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..

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

Could you give me the formulas as you did in my previous post?
--
Templar


"Pete_UK" wrote:

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

On Oct 2, 4:57 pm, Templar wrote:
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

--
Templar

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

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

On Oct 2, 5:51*pm, Templar wrote:
Could you give me the formulas as you did in my previous post?
--
Templar