Microsoft access parse address

If the EmailAddress field could be Null, you'll probably need to amend those to forceit to be converted to an empty string, like this:. Threats include any threat of suicide, violence, or harm to another. Any content of an adult theme or inappropriate to a community web site. Any image, link, or discussion of nudity. Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect. Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software.

Unsolicited bulk mail or bulk advertising. Any link to or advocacy of virus, spyware, malware, or phishing sites. Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct. Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation. Details required : characters remaining Cancel Submit.

Search within: Articles Quick Answers Messages. Tagged as VB. Stats 8. Clifford Nelson Rate me:. Please Sign up or sign in to vote. VBA Code that will take a string and attempt to pull out the company name, street address, city, state, zip code, phone number, and web address. Introduction I have been manually entering address information in a Microsoft Access application I have for years, and basically all I have been doing is reparsing information that I can just copy bulk from the web.

NOTES The way I use this code is to give preliminary values, and expect to have to edit some of the values to make corrections. Limitations This code is pretty basic and has many limitations: The company name is expected to be the first information after removing white space in the string and ends with either a comma or a carriage return.

Once extracted, the information is removed so that it will not interfere with later operations. The phone number is extracted with a regular expression. It is one of the first things done, and once extracted, the phone information is removed so that it will not interfere with later operations.

Likewise, the web address is extracted with a regular expression. Regexes to implement that would be nuts. Furthermore: when you do send out the stuff in the mail that's what it's for, right? We made a simple gui for the front desk person to do that; the person who actually sorts through the mail.

After the advice here, I have devised the following function in VB which creates passable, although not always perfect if a company name and a suite line are given, it combines the suite and city usable data.

Passing the parseAddress function "A. I've been working in the address processing domain for about 5 years now, and there really is no silver bullet. The correct solution is going to depend on the value of the data. If it's not very valuable, throw it through a parser as the other answers suggest.

If you're looking for a fully automated, repeatable solution, you probably want to talk to a address correction vendor like Group1 or Trillium. SmartyStreets has a new feature that extracts addresses from arbitrary input strings. Note: I don't work at SmartyStreets. It successfully extracted all addresses from the sample input given in the question above.

By the way, only 9 of those 10 addresses are valid. Here's some of the output:. I was the developer who originally wrote the service. The algorithm we implemented is a bit different from any specific answers here, but each extracted address is verified against the address lookup API, so you can be sure if it's valid or not.

Each verified result is guaranteed, but we know the other results won't be perfect because, as has been made abundantly clear in this thread, addresses are unpredictable, even for humans sometimes. Good suggestion, alternatively you can execute a CURL request for each address to Google Maps and it will return the properly formatted address.

From that, you can regex to your heart's content. My general answer is a series of Regular Expressions, though the complexity of this depends on the answer.

And if there is no consistency at all, then you may only be able to achieve partial success with a Regex ie: filtering out zip code and state and will have to do the rest by hand or at least go through the rest very carefully to make sure you spot the errors. That will leave you with the address mess. MOST All? That will be your address. Anything to the left of that number is likely an addressee.

For the address, check for the existence of "Suite" or "Apt. For the addressee I would punt and grab the last word of that string as the last name and put the rest into the first name field. If you don't want to do that, you'll need to check for salutation Mr. Try www. We use their web service, which you can test online. I would start at the end of the string. Parse a Zip-code either format.

Read end to first space. If no Zip Code was found Error. Then move on to State, again use the Space as the delimiter. Maybe use a lookup list to validate 2 letter state codes, and full state names. If no valid state found, error. City gets tricky, I would actually use a comma here, at the risk of getting too much data in the city. Look for the comma, or beginning of the line.

If it's human entered data, then you'll spend too much time trying to code around the exceptions. You might want to check this out!! Here is the output for one of your sample addresses. Note that the name section would need to be removed first from "A. This is easily achieved by removing all data up to the first number found in the string. I have been using this on one of my project and it does the work I need. The only Issue I had was whenever an address is in this format P.

Box" with '' and after this it were able to parse it. There are data services that given a zip code will give you list of street names in that zip code.

Use a regex to extract Zip or City State - find the correct one or if a error get both. Once you get a valid Address line 1, city, state, and zip you can then make assumptions on address line If you are strictly in the US Now look for these in your addresses. You can validate what you find by testing if, say, the city you found exists in the state you found, or by checking if the street you found exists in the city you found.

If not, chances are John isn't for John's street, but is the name of the addressee Basically, get the most information you can and check your addresses against it. It's regex-based and works fairly well.

Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. Asked 13 years, 5 months ago.