vlookup question

[Greegan]

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

[Bob Umlas]

Please don't cross-post

"Greegan" <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>

[swjtx]

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

[Greegan]

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" <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>