Splitting cells

[Albee]

I have a huge column of address info. All the info is only seperated by
spaces not comas. Each address is a different length so i cant even split by
using "spaces" because when seperated into seperate columns info does not
match (I.E. adress info straight down in one column, city or state in one
column). I need to have them seperated so I can do a mail merge. Can anyone
help me? Am i going to have to manually seperate each address by comas??

 

[Ron Rosenfeld]

I have a huge column of address info. All the info is only seperated by
spaces not comas. Each address is a different length so i cant even split by
using "spaces" because when seperated into seperate columns info does not
match (I.E. adress info straight down in one column, city or state in one
column). I need to have them seperated so I can do a mail merge. Can anyone
help me? Am i going to have to manually seperate each address by comas??

If you can devise an unambiguous rule for splitting your data, it could likely
be implemented.

Is there any consistency in the way entries are made?

Are there zip codes that could be used to do a lookup to determine the city and
state?

Is there also a NAME in with the address info? If so, how is the name
separated from the street?

--ron

 

[Albee]

Here are Just a few examples of the hundreds of addresses i have in one
column...

61 Associates #12 Attn Bill Ritzel 350 Fifth Avenue Ste 3410 New York, NY
10118
A Highmark LLC C/O Marks Paneth & Shron LLP Attn Phyllis Jaffee 622 Third
Ave 7th Fl New York, NY 10017
A Paul Victor And Ellen G Victor T.I.C 50 East 79th Street Apt 15c New York,
NY 10021
A Paul Victor 50 E 79th Street Apt 15c New York, NY 10021
Aaa Garment Delivery Inc Pension Plan & Trust 242 West 36th Street New York,
NY 10018

I need the above info to be seperated into seperate columns so can do a
label mail merge.... It should be seperated into columns that are shown
below...

First name Last name Company Street Address City State ..... and so
on...


So basically there is no consistency with the address info...Does this
help?? thank you for responding to the first post...

 

[Ron Rosenfeld]

Here are Just a few examples of the hundreds of addresses i have in one
column...

61 Associates #12 Attn Bill Ritzel 350 Fifth Avenue Ste 3410 New York, NY
10118
A Highmark LLC C/O Marks Paneth & Shron LLP Attn Phyllis Jaffee 622 Third
Ave 7th Fl New York, NY 10017
A Paul Victor And Ellen G Victor T.I.C 50 East 79th Street Apt 15c New York,
NY 10021
A Paul Victor 50 E 79th Street Apt 15c New York, NY 10021
Aaa Garment Delivery Inc Pension Plan & Trust 242 West 36th Street New York,
NY 10018

I need the above info to be seperated into seperate columns so can do a
label mail merge.... It should be seperated into columns that are shown
below...

First name Last name Company Street Address City State ..... and so
on...


So basically there is no consistency with the address info...Does this
help?? thank you for responding to the first post...

It helps. But, unfortunately, it only helps with showing how difficult the
task would be to automate.

1. Separating out the Zip Code is relatively simple, since it is the last
"word" in every string:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",99)),99))

2. You can obtain the correct city, state by doing a Zip Code lookup, either
from a table that you have loaded someplace in Excel, or by using automation
and looking it up on the Internet. Looking it up at the USPS site on the
Internet, automatically through Excel, would be quite time-consuming.

a. IF all of the City,State combos are entered in the USPS required
format (highly unlikely in my opinion), you could then separate out the first
part of the address with:

=LEFT(A2,SEARCH(cell_ref_containing_City_State,A2)-1)

3. I see no simple method of separating out the rest. It's a pattern
recognition problem and you'd have to code all the various patterns. It'd
probably be a lot simpler to hire someone to just enter the addresses properly
into a form, so you can do your mail merge.
--ron

 

[Albee]

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

:

 

[Ron Rosenfeld]

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