Address cleansing using VBA

[zacherry]

Hi all, thanks for your help. I have a spreadsheet that uses INDEX(MATCH()) to validate data fields between two databases that I maintain. I want to get rid of false positives in checking addresses by Find and Replacing common address suffixes to standardize the data in both sets. I found a list of common incorrect suffixes and their correct counterparts and scraped it off of the USPS website and into Excel. I want to manipulate a macro to search any part of the address field and find and replace the full or common suffix with the USPS standard suffix. An example would be: Circle with Cir. I have a macro that will only find the suffix if it's alone in a field. I need it to search any part of a field. Can you help? Here's what I'm working with:

Please Login or Register  to view this content.

Attached is a list of the suffixes I want to cleanse. Suffixes.xlsx

[Richard Buttrey]

Hi,

Just a thought. Have you tried incorporating the wildcard character *. e.g

Please Login or Register  to view this content.

...

Incidentally I'm intrigued to see you've used the colloquialism 'nowt' for nothing. Is that a regular expression in CA?
Over here it's generally associated with people from Yorkshire, aka God's own county (and the UK's largest) and most commonly heard in the expression

If ivver tha does owt fir nowt - do it fir thissen. (In translation: If you ever do something for nothing, do it for yourself).

[zacherry]

Swiped that bit of code from here: http://www.mrexcel.com/forum/excel-q...-function.html

Maybe you and Mr. WillR have a thing or two in common. I haven't heard that expression around these parts.

When I record the macro for one instance of the thing I want it to do, it looks like this:

Please Login or Register  to view this content.

How do I scale that up/ mash it in with the previous bit of code to get it to work for all of the entries in a list?

Thanks!