• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Physical address validation

GBurke

New Member
Hi there gurus,

I am looking for an Excel solution for address validation, for Australia.

Specifically, if a user types in an address I wish to check that the address exists, and if it doesnt, or if there is a typo such as street name McKenzie and not Mackenzie, or that address doesnt exist in that State (maybe the State entered is incorrect) so that a correction can be made at the time the data is entered.

I suspect there are a number of ways to do this, but not sure which would be the most efficient way.

It would be great if someone had a working solution, but I am also interested in knowing the hows and whys. I am ok with using an API that I would need to pay for, but would prefer a try before you buy option so I can test to see if it does what I need it to do.

I have an App that uses Application.FollowHyperlink "http://maps.live.com/default.aspx?where1=" & strAddress but I dont know how to use that for validation purposes. I can type in a typo and the address is found ignoring the typo. In other cases, if the address is not found, an error advising as such is not found is displayed. I just use that code to display the map.

Any help would be greatly appreciated.
 

Chihiro

Excel Ninja
First step is to find reputable source to validate data against.

In general, if you search for the address (using google) and it returns page with <span class="desktop-title-subcontent"> and if it holds City, Region Code, Postal code and country. It's likely valid address.

But since google may have fuzzy lookup / AI logic in search engine, you may want to find more rigid data base / web service.

If using google. Simple method is to pilot browser via VBA and read content of the page and locate desired HTML element.

Ex: Using following URL to search for address.
https://www.google.com/search?q=18+Aitken+road+bowral

I don't have time today to write a sample code. But if you provide 5 sample address (mix of valid ones and incorrect ones), I can see if I have time this week to write a sample code.
 

GBurke

New Member
Hi Chihiro,

Thanks for the response. Much appreciated.

Here are a few addresses
12 Hope Street Glen Iris VIC 3146 - valid address - Google Maps reports address as 12 Hope St
12 Hoep St Glen Iris Vic 3146 - invalid address (typo with the street name) - but Google maps finds the correct address
62-64 Menangle Street Picton New South Wales 2517 - invalid address (post code is wrong) - Google maps reports "partial match" and shows the correct address, with correct post code.
62-64 Menangel Street Picton VIC - invalid address (street name incorrect, State incorrect) - Google maps lists several possibilities
38 Adelaide Rd Mount Barker South Australia 5251 - valid address - Google maps reports as entered
38 Adelaide Road Mt Barker South Australia 5251 - valid address - Google maps reports Rd not road and Mount Barker not Mt Barker

It would appear that Google maps is using fuzzy logic, which is great, whereas a more rigid platform may not find as many "matches".

Entering these same addresses directly into Google (not via Google maps) appears to generate the same results.
74393 shows correct street spelling
74394 shows correct post code
 
Top