vlookup question

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

This is quite a complex issue and is a real pain in programming.

You need to parse, that is go through the line and extract the city and the
address.

I do not have any magic solution and it is just a matter of programming and
code to decide the best way to do it.

Sorry, not much help.

But may be somebody has some code they are willing to share.



"Greegan" wrote:

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

It all depends on the cities that are included. Can you state in advance
that they must be from a fixed list of known cities, or could it be any city?
If you can't enumerate all the possibilities in advance this could be nearly
impossible. However, if you know the list of possible cities, and if it is
not too long, then yes, you could try doing a Find based on a list. But if
the list is very long this would also take a very long time to process. So
no matter what it will be difficult and is likely to take a long time to run.
No easy way around this.
--
- K Dales


"Greegan" wrote:

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

In regards to a specific list of cities, I have a total of 50041 city
names in my list.

Basically I took a list of zip codes, removed duplicate names from
different states so that it only appears once in the list.

I was hoping I could do some sort of vlookup to compare the contents of
each cell in a specific column (column D for example) and do a replace
where all it really does is add a comma before and after the city name.
So in my workbook I will have a list of these city names, but will need
a way to compare the list to what exists in column D...

I hope that narrows down the problems you would see encountering.

*** Sent via Developersdex http://www.developersdex.com ***