Hey Joel...if routine can handle the examples given that will handle 99.5% of
the data and I will throw out the rest. It does not have to be 100%.
Currently I'm getting 90%. I can write something, it will take me some
time....but I thought there were some experienced guys out there who could
slam dunk this no problem. I can do it easy in my other language, but am new
to Excel VBA.

"Joel" wrote:

Is this data text data before it was imported into excel. It appears this
data went through some program that created this mess. Do you have the data
before this mess was created. there is no good way of getting STREET out of
the 2nd example unless you search for certain key words being by themselves
lis Street, Ave. Avenue.

You included only a small portion of the lines so without seeing a lot of
data I can't predict what types of algorithms will or will not work. I would
be guessing.

If this data was hand typed and we are trying to correct human errors than
no algorithm will work all the time because humans are not predictable, only
machines are predictable. We can only try are best to fix as many problems
as possible with a macro when humans are the causes of the problems.

"Carl S." wrote:

Someone brilliant have code to parse messy Address City State ZIP into 4
separate fields? If address has Apt number it should NOT be parsed separate
but included in address fields. Here are examples we get of data downloaded
daily...the main common theme is CA is consistent:

15629 FAIRFORD AVENUE, NORWALK, CA 90650
5947-5949 SOUTH SAN PEDRO, STREET, LOS ANGELES, CA 90003
Vacant Land, Long Beach, CA
4627-4627 1/2 STRANGE AVENUE, Los Angeles (Area), CA 90022
15522 SYLVAN STREET, (VAN NUYS AREA) LOS ANGELES, CA 91411
4325, 4327 & 4329 Stern Ave., North Hollywood, CA 91423
19200 LAHEY STREET, UNIT 4,, Los Angeles, CA 91326

Example 1 is typical easy parse, majority of data.
Example 2 the word STREET should not have been separated by comma.
Example 3 has no zip code or street numbers.
Example 4 has dash and / in street numbers and (AREA) should be removed.
Example 5 (VAN NUYS AREA) should be removed.
Example 6 has extra comma in address at front.
Example 7 should have UNIT 4 glued to address, and extra commas need removal.

If someone can write something to handle all of this, you are awsome! If
you can get everything except example 3 that is ok....I can strip those out
ahead of time...they either say VACANT LAND or RAW LAND...but I would like to
have them if possible. Thanks in advance!

Parse it back-to-front
Take off the zip, if it exists plus any spaces and comma
then the state, if it exists, and any space/comma before it
then look at the next comma-separated section.
If it exists, take it and remove parenthetical text as the city.
What's left is the address...
This seems to meet your requirements.

You can also vet the state by word, abbrev, or start-of-word.
You can vet the city from a larger list, as well as lookup city by
zip, given the right list.

Thanks, I am thinking along those lines too. Problem is I don't have to the
technical know how to do it easily. Kind of like knowing how a jet engine
works but unable to write a manual on it in Japanese. Oh well. Guess I have
to tough it out, nothing in life is free but I have got some great snippits
here in the past. Problably take me two days to write the code. Was hoping
someone had a routine for this they could slam dunk to me.

"ras" wrote:

Parse it back-to-front
Take off the zip, if it exists plus any spaces and comma
then the state, if it exists, and any space/comma before it
then look at the next comma-separated section.
If it exists, take it and remove parenthetical text as the city.
What's left is the address...
This seems to meet your requirements.

You can also vet the state by word, abbrev, or start-of-word.
You can vet the city from a larger list, as well as lookup city by
zip, given the right list.