• 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.
 
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.
 
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
 
Try something like below.

Note: I didn't make it fully dynamic, and code lacks error trap. You may want to consider adjusting code as you test it for your need.

EDIT: Oh I forgot to mention that you may want to add Microsoft HTLM object library reference in VBA project.

Code:
Sub Demo()
    Dim baseUrl As String: baseUrl = "https://www.google.com/search?q="
    Dim addrStr As String, stStr As String, cityStr As String
    Dim cel As Range
  
    For Each cel In Range("A2:A7").Cells
        addrStr = Join(Array(cel.Value, cel.Offset(, 1).Value, "Australia"), " ")
        addrStr = Replace(addrStr, " ", "+")
        With CreateObject("InternetExplorer.Application")
            .navigate baseUrl & addrStr
            .Visible = False
            While .readyState < 4 Or .Busy: DoEvents: Wend
            stStr = .document.getElementsByClassName("desktop-title-content")(0).innerText
            cityStr = .document.getElementsByClassName("desktop-title-subcontent")(0).innerText
            cel.Offset(, 4) = stStr & vbNewLine & cityStr
            .Quit
        End With
    Next
End Sub
 

Attachments

  • AddressSuggestion_Pilot_IE.xlsm
    18 KB · Views: 22
Last edited:
That is fantastic - exactly what I needed.
Yes, I have added some error checking and added the library reference as suggested.

Looks like I need to read up on the GetElementsnnnnnnn
 
Ooops, spoke a bit too soon.
Try these 2 addresses. Both are valid addresses, yet an error is reported. Err 91,
Object variable or With block variable not set

1 Civic PlaceDeniliquinNSW
2710​
CNR DAVEY & YOUNG STREETFrankstonVIC
3199​

74414

74413
 
Hi,​
the question is why not to use an address checker specific website rather than the poor Google ?​
 
Mi Marc L
Do you have any suggestions as to which address checker specific website could be used?
 
No idea as this is not my country but with an easy basic web search you can find some then test according to your need.​
Once you made your choice, give us a feedback in order to see if it can be used under VBA …​
 
GBurke
Have You 'googled' ed address validation australia free?
... or without that 'free'?
There seems to be many ... for You.
 
GBurke
You wondered ...
Do you have any suggestions as to which address checker specific website could be used?
... then You should check Yourself which You could use.
 
Address, is finicky to validate when trying to fully automate. Personally, I'd just add error trap and those that return error, have user manually check.
Google or any other database, shouldn't matter in this case.

FYI - CNR Davey & Young st, isn't really an address but an intersection. If it's Frankston Arts Centre, the actual address is 27-37Davey St, Frankston VIC 3199.
 
Hey Chihiro, I think I agree with you. I have tried a few other approaches as suggested by others.
I have already adopted your approach - error trap those that I can't validate.
In relation to "corner of" addresses. I have adopted a Google Maps approach and they return intersection. Which I suspect is the correct apprach as there would be 4 corners at an intersection, so in the example above I may not want the Frankston Arts Centre but an address on one of the other corners.
Thanks for your help.
 
Back
Top