How To Parse an Address in Excel by Macro/VBA

If you have a full address and would like to extract address line, city, state (province), country and zip code (postal code), then you came to the right place. In this blog, we explain how to make an excel file to parse address. If you’re interested in a custom solution for your Excel file, please contact our Excel experts for a free quote.

We used Google Maps API to extract the information from full address. Google has a special address structure. When you run the code, both Google’s address and regular address is displayed.

Demo

Feel free to download a demo of the Excel XLSM file.

How the Excel file works?

  1. In range C1, type the address that you want to parse, for example “1500 Marine Drive, North Vancouver, BC”.
  2. Click “Parse Address” button. In the yellow area marked by “Google Map Address Structure”, the processed address by Google will be displayed.
  3. The green area marked by “Regular Address” shows the actual address divided into
    • Address
    • City
    • State/Provide
    • Country
    • Postal/Zip Code

Development

If you are familiar with VBA Editor and creating buttons, you can copy/paste the following VBA code into your VBA editor. And create a button to execute this function.

Then, install Json Converter developed by https://github.com/VBA-tools/VBA-JSON. i.e. create a module called “JsonConverter” and copy/paste the code written in the Github link in the module.

Once you copy/paste the code and created a button, type an address in range C1 and click the button. The address is parsed!

VBA Code

' Written by bsup.ca

' Extract Full Address to
' Street Number, Route, Neighborhood, Locality, Administrative Area, Country and Postal Code
Sub ExtractAddress()
    Dim sURL As String, sResult As String
    Dim oResult As Variant, oData As Variant, R As Long, C As Long

    ' Clear the sheet
    ActiveSheet.Range("B2:C9").ClearContents
    
    sURL = "http://maps.googleapis.com/maps/api/geocode/json?address=" + _ 
        WorksheetFunction.EncodeURL(Range("C1").Value) + "&sensor=false"
    
    Debug.Print "URL: " & sURL
    sResult = GetHTTPResult(sURL)
      
    Dim Json As Object
    Set Json = JsonConverter.ParseJson(sResult)
     
    Dim element As Variant
    
    R = 1
    
    For Each element In Json("results")(1)("address_components")
        ActiveSheet.Cells(R + 1, 2) = element("types")(1)
        ActiveSheet.Cells(R + 1, 3) = element("long_name")
        R = R + 1
    
    Next element
    
    Set oResult = Nothing
End Sub

' Get the json result
Function GetHTTPResult(sURL As String) As String
    Dim XMLHTTP As Variant, sResult As String

    Set XMLHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    XMLHTTP.Open "GET", sURL, False
    XMLHTTP.Send
    Debug.Print "Status: " & XMLHTTP.Status & " - " & XMLHTTP.StatusText
    sResult = XMLHTTP.ResponseText
    Debug.Print "Length of response: " & Len(sResult)
    Set XMLHTTP = Nothing
    GetHTTPResult = sResult
End Function

Published by

bSuperior System Ltd Experts

We are experts in customizing Excel files and VBA programming. We create Excel files for specific needs. We develop VBA programs to automate tasks and execute processes automatically . We are based in Vancouver BC, Canada.

Leave a Reply

Your email address will not be published. Required fields are marked *