Hello,

On an spread sheet on a single column I receive rather mixed up address that
I need to separate the street address into one column and city in another and
state into 3rd column and off course zip on 4th column... Here is some off
the address I receive:
1295 GAUGUIN CIR ALISOVIEJO CA 926563875
93316 S ARAPAHO DR SANTA ANA CA 927042403
9345 GAUGUIN Cm ALISOVIEJO CA 926563875
2344268 ORANGE AVE APT 3 LAKE FOREST CA 926304886
361449 S EL C AMINO REAL APT 4 SAN CLEMENTE CA 926723451
235521 EL REPOSA ALISOVIEJO CA 926561109
106556 CAIXE DEL CERRO UNIT 1414 SAN CLEMENTE CA 926726075
204475 S BIRCH ST SANTA ANA CA 927072703
125542 HALLADAY ST SANTA ANA CA 927071407
103319 W MYRTLE ST SANTA ANA CA 927033911
3342 ENTERPRISE APT 4106 ALISOVIEJO CA 926567091
6100EDINGER AVE APT 326 HUNTJNGTNBCH CA 926473266
As you may have noticed not only there are a lot of errors on the spelling
but also city names sometimes come in two words i.e. Santa Ana or ALISOVIEJO
which should be "Aliso Viejo" and as you see sometimes "6100 Edinger" comes
like "6100EDINGER"

I search for "Split Address" on the Forum and I did not see anything that
can tackle such a complicated situation... Splitting each Word into a
separate column does not work because sometimes city names are two word as I
explained above.

I know I need to do some kind of hand cleaning before/after I run any
special macro that can handle such a task but at this time I am kind of
baffled how to this huge task...

I came up with idea that if I scan the address column and take the last
piece of character with a leading and trailing space i.e. 926473266 and stick
it into Zip column and prior leading/trailing space as State and stick it
into State Column then I have one more to strip of the Address strings and
that would be City name which unfortunately because I have miss spelling and
sometimes two word as city name I have problem€¦

For city name sometimes I have to pick up two words prior to State and Zip
sometimes only one word and sometime I have spelling problem€¦

On line one of the sample address above for example 927042403 can be picked
up and stick into Zip code column, CA can be picked up and stick into State
Column but there is no city name by the name of ANA in California so I have
to pick up Word before ANA too which is SANTA ANA and stick it into city name
column and for the last example address line HUNTJNGTNBCH has spelling
problem along with two word city name and the correct name is Huntington
Beach or Bch€¦.

A macro that can look up the zip code and give me a spelling suggestion or
correct city name would be awesome.

Honestly programming such a complicated macro is way above my knowledge of
VBA programming strengths. I thought if I posted it somebody may give me
some suggestion or there is an already Add-in macro exist etc to solve such a
problem€¦

Regards

--
Jeff B Paarsa