Is there a way to validate a list of addresses in Excel that is free of charge? I have a list of addresses that were put in by various individuals that I would like to standardize. For example, I would like to change all of the states that are spelled out to state abbreviations. I would like to take the words Street, Drive, Avenue, etc and convert them to abbreviations as well. I would also like to convert all the addresses to all upper case. Does anyone know of a way to accomplish this without having to pay a monthly fee or a fee per address?
Address Validation (1 Viewer)
- Thread starter LadyDi
- Start date
isladogs
MVP / VIP
- Local time
- Today, 07:46
- Joined
- Jan 14, 2017
- Messages
- 18,211
You can do all of that using code
1. Add a table of states and abbreviations. Use an update query to replace.
2. Ue a series of Replace functions to replace Street with St .... Etc
3. Use StrConv function with argument 3 for proper case
However, I'm not sure how this amounts to validation
EDIT
Oops -the above applies to Access
Someone else can advise on doing this in Excel
Perhaps using Vlookup for step 1.
The other two steps may also be fine in Excel
1. Add a table of states and abbreviations. Use an update query to replace.
2. Ue a series of Replace functions to replace Street with St .... Etc
3. Use StrConv function with argument 3 for proper case
However, I'm not sure how this amounts to validation
EDIT
Oops -the above applies to Access
Someone else can advise on doing this in Excel
Perhaps using Vlookup for step 1.
The other two steps may also be fine in Excel
Last edited:
You are correct, this is not really validating the address, it is just standardizing it.
I downloaded a list of acceptable abbreviations from the Post Office. Could someone tell me a way to update a list of addresses with these standard abbreviations without doing a formula that is nine miles long (a replace for every possibility). Would there be a macro (or a formula other than Replace) that would run down a list and replace any word found from column A with the abbreviation in column B?
I downloaded a list of acceptable abbreviations from the Post Office. Could someone tell me a way to update a list of addresses with these standard abbreviations without doing a formula that is nine miles long (a replace for every possibility). Would there be a macro (or a formula other than Replace) that would run down a list and replace any word found from column A with the abbreviation in column B?
This is not a one off. We receive applications daily from a website. We want to download the addresses, correct them and then re-load them to the site (unfortunately, we don't have permissions to regulate the fields on the website). Unfortunately, I don't have the option of using Access for this project. I have to do the operation on a server and Access is not installed on the server and I don't have access to install it.
Similar threads
