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.
--
God Bless! Cindy

You could use formulas like (=left(c1,len(c1)-3) to get everything but the
last comma and two numbers, then =right(c1,2) to get the last two numbers.)
Drag these formulas down, then highlight them and go to Edit-Copy,
Edit-Paste Special Values to remove the underlying formulas. Then keep
repeating the process for each column of data you want to pull of from the
original data...

"Cindy" wrote:
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.
--
God Bless! Cindy

Hi Cindy-

Which ver. of Excel are you using? Might make a difference in how TtC works,
but I don't think so.

Using Excel '03, I tried what you're having a problem with and seemed to
have excellent results.

1) Started with this in one cell: |1015 Bond St., Suite 299, Baltimore, MD
21227|

2) Used Text to Columns, Delimited, set delimiter to Comma & removed check
for Space

This gave me |1015 Bond St.|Suite 299|Baltimore|MD 21227| in 4 separate
colums. Next:

1) selected the cell w/State & Zip

2) Text to Columns, Delimited, set Delimiter to Space & removed check for
Comma, and in Step 3 clicked to select first column & set option to Do Not
Import (Skip)

3) final result: |1015 Bond St.|Suite 299|Baltimore|MD|21227| in 5 columns
with no further editing to be done (the last step of the Wizard isolates the
leading space in the State cell as the column to not be imported).

Granted, this puts Street Address & Suite in separate columns, but IMHO that
is preferable for mail merge as well as most other purposes.

Hope this is useful |:)

"Cindy" wrote:

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.
--
God Bless! Cindy