On Mon, 2 Mar 2009 11:08:04 -0800, Albee
wrote:

Thank you so much for helping out Ron...

So if I understand you correctly, there is no way to "trim" or seperate the
city state and zip code from the column? Well i guess since all the cities
and states will be New York, NY i can just enter those colums manually and
then drag all the way down.

Now the only thing is that when i used the first formula you gave me to
seperate the zip codes, it did do it, however it repeated it not actually
seperate it. Basically if i were to do a mail merge it would appear twice
since its still part of the address in the first column and entered in the
next column. So the sheet looks something like this if it helps:

Column A Column B
"Address info"..... zip code Zip Code (formula you gave)


I havent tried the second one yet but would it repeat the same info as the
first formula?

Hope im not confusing you Ron... Thank you so much agian for your help...

If ALL the cities and States will be New York, NY; and if they ALL have Zip
Codes, then I think the best you can do is this:

Column A will be your "Source Column"; It will not be split, but can be
"ignored".


Column B: Combined Adressee/Street Address/Apt or Suite/Etc

Column C: City
Column D: State
Column E: Zip

B2: =LEFT(A2,SEARCH("New York,",A2)-1)
C2: New York
D2: NY
E2: =TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

Select B2:E2 and fill down as far as required.

Splitting the information in Column B will need to be done manually.

If you want to completely ignore (and and /or delete) column A:

Select B1:En
Edit/Copy
Edit/Paste Special/Values

You can now safely delete column A.
--ron