I am given a list of contacts and their addresses in a file I need to sort
(the columns being rearranged) into a special order...

My issue is that some of these files come with the street address, city,
state and zip codes all in one cell.

The state and zip codes are easy to separate them I have setup a script for
that, replacing STATECODE with ,STATECODE, which works great.

My problem is separating the city name and the street address.

Someone asked if i could just do a vlookup and compare the contents of the
address list with the vlookup list... I'm guessing its possible but i'm not
sure how that could be done.

Could someone give me some assistance with this please.

Thank you in advance,

G

Please don't cross-post

"Greegan" wrote in message
...
I am given a list of contacts and their addresses in a file I need to sort
(the columns being rearranged) into a special order...

My issue is that some of these files come with the street address, city,
state and zip codes all in one cell.

The state and zip codes are easy to separate them I have setup a script
for
that, replacing STATECODE with ,STATECODE, which works great.

My problem is separating the city name and the street address.

Someone asked if i could just do a vlookup and compare the contents of the
address list with the vlookup list... I'm guessing its possible but i'm
not
sure how that could be done.

Could someone give me some assistance with this please.

Thank you in advance,

G

Hi,

I've had this problem before. What I ended up doing was copying the
data into notepad and manually inserting a comma between fields then
exporting it back into excel as "comma deliminated".

Not the crispest method but that's all I could come up with.

I think that you could do a lookup for city names but you would have to
have a table of city names which would only get the name into another
cell, not out of the cell with the address.

If there is a space between everything like:

1212 York St. Westhampton, WA 02996

You could put it into notepad and do a find/replace of all " " (spaces)
with a comma then import it into excel as "comma deliminated" but then
each part would be in a separate field. There may be a way to merge the
fields after you do this to get the street number, street name and
"St." into the same field leaving the City, State and zip in separate
fields.

If I were you, I would wait for other replies. I don't claim to be an
expert and maybe there is a better way.

swjtx


--
swjtx
------------------------------------------------------------------------
swjtx's Profile: http://www.excelforum.com/member.php...o&userid=29716
View this thread: http://www.excelforum.com/showthread...hreadid=494443

Okay I misunderstood what a friend of mine said...
Now he says I should be able to do a "find script"
Says it shouldn't be more than 36 lines or so... If I figure it out before
someone here helps me, I will post it (if that's allowed)

Thanks again

G



"Greegan" wrote in message
...
I am given a list of contacts and their addresses in a file I need to sort
(the columns being rearranged) into a special order...

My issue is that some of these files come with the street address, city,
state and zip codes all in one cell.

The state and zip codes are easy to separate them I have setup a script
for
that, replacing STATECODE with ,STATECODE, which works great.

My problem is separating the city name and the street address.

Someone asked if i could just do a vlookup and compare the contents of the
address list with the vlookup list... I'm guessing its possible but i'm
not
sure how that could be done.

Could someone give me some assistance with this please.

Thank you in advance,

G