Do you have an address and are looking for extracting the address line, city, state (province), country and zip code (postal code)? In this blog, we will explain how to make an Excel file to parse an address. If you’re interested in a custom solution for your Excel file, please contact our Excel expert consultant team for a free quote.
Google API Key
We used Google Geocoding API to extract the information from an address. To use Google API, you’re required to have a Google API key.
Feel free to download a demo of the Excel XLSM file.
How the Excel file works?
- In range C1, type the address that you want to parse, for example “1455 Crown St Ste A, North Vancouver, BC”.
- In range C2, enter your Google API key.
- Click “Parse Address” button. In the yellow area marked by “Google Map Address Structure”, the processed address by Google will be displayed.
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 your Google API key in range C2. Then click the button. The address is parsed!
If you’re looking for more help regarding your Excel files, please contact us through Excel services page.
' Developed by bsuite365.com
' Extract Full Address to
' Street Number, Route, Neighborhood, Locality, Administrative Area, Country and Postal Code
Dim sURL As String, sResult As String
Dim oResult As Variant, oData As Variant, R As Long, C As Long
' Clear the sheet
sURL = "https://maps.googleapis.com/maps/api/geocode/json?address=" + _
WorksheetFunction.EncodeURL(Range("C1").Value) + "&sensor=false" + "&key=" + Range("C2").Value
Debug.Print "URL: " & sURL
sResult = GetHTTPResult(sURL)
Dim Json As Object
Set Json = JsonConverter.ParseJson(sResult)
Dim element As Variant
R = 2
On Error GoTo Google_Err
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
Set oResult = Nothing
MsgBox "Macro got a problem in connecting to Google API. " & _
"Please make sure yor Google API key is valid and working."
Set oResult = Nothing
' 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
Debug.Print "Status: " & XMLHTTP.Status & " - " & XMLHTTP.StatusText
sResult = XMLHTTP.ResponseText
Debug.Print "Length of response: " & Len(sResult)
Set XMLHTTP = Nothing
GetHTTPResult = sResult
Our experts will be glad to help you, If this article didn't answer your questions.