G
Guest
I am working on a home PC, Excel 2003. I have a column (it is column C of A
thru K, if that matters) of data that includes street address (may include
suite number, etc.), city, state, zip and all info is separated by commas
except state and zip. I am trying to separate the information in this column
so that each part is in its own column. I need to keep all of the address
(street number, street name, PO box number, suite number, etc. together) in
one field, city in the next field, then state, then zip. The problem I am
having when converting text to columns using the comma as the delimiter is
that I end up with too many columns of data. Is there a way to do something
like this starting with the comma farthest to the right and only going back
five characters to get the zip out and then continuing from right to left for
two commas to strip the state out then the city, leaving the rest to be the
address?
Any help is greatly appreciated. My only alternative is to retype all info
in columns A through K, rows three through 1,102.
thru K, if that matters) of data that includes street address (may include
suite number, etc.), city, state, zip and all info is separated by commas
except state and zip. I am trying to separate the information in this column
so that each part is in its own column. I need to keep all of the address
(street number, street name, PO box number, suite number, etc. together) in
one field, city in the next field, then state, then zip. The problem I am
having when converting text to columns using the comma as the delimiter is
that I end up with too many columns of data. Is there a way to do something
like this starting with the comma farthest to the right and only going back
five characters to get the zip out and then continuing from right to left for
two commas to strip the state out then the city, leaving the rest to be the
address?
Any help is greatly appreciated. My only alternative is to retype all info
in columns A through K, rows three through 1,102.