Geocoding is the process of converting addresses to the x and y coordinate or longitude and latitude. There are various option available in the market to perform geocoding, some are paid and some are free. You can use The Google Maps Geocoding API to retrieve the longitude and latitude for free. Later you can convert this longitude and latitude to your desire coordinate values, there are number of online tools for doing that. So it will be hassle to perform each addresses one at time in google, for that you can use Excel software to perform large number of addresses. You create excel function to perform the geocoding for multiple addresses.

For this task I am using Microsoft Excel 2013, you can use 2007 or 2010 version. First task is to turn on the Developer tab. To find this tab in 2007 or 2010 version you have to play around.

1. Go to the Microsoft File > Options > Customize Ribbon and check Developer

You will notice Developer tab is added on the ribbon.

2. Now click the Visual Basic button on the far left corner which will open the new window. Now we want a new module to insert which will store our new function. For that click on Insert > New Module

Copy the below code and paste into the empty area (code source:

Function MyGeocode(address As String) As String
  Dim strAddress As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String
  strAddress = URLEncode(address)
  'Assemble the query string
  strQuery = ""
  strQuery = strQuery & "address=" & strAddress 
  strQuery = strQuery & "&sensor=false"
  'define XML and HTTP components
  Dim googleResult As New MSXML2.DOMDocument
  Dim googleService As New MSXML2.XMLHTTP
  Dim oNodes As MSXML2.IXMLDOMNodeList
  Dim oNode As MSXML2.IXMLDOMNode
  'create HTTP request to query URL - make sure to have
  'that last "False" there for synchronous operation
  googleService.Open "GET", strQuery, False
  googleResult.LoadXML (googleService.responseText)
  Set oNodes = googleResult.getElementsByTagName("geometry")
  If oNodes.Length = 1 Then
    For Each oNode In oNodes
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
      MyGeocode = strLatitude & "," & strLongitude
    Next oNode
    MyGeocode = "Not Found (try again, you may have done too many too fast)"
  End If
End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
  Dim StringLen As Long: StringLen = Len(StringVal)
  If StringLen>0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String
    If SpaceAsPlus Then Space = "+" Else Space = "%20"
    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)  
      CharCode = Asc(Char)
      Select Case CharCode
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
        result(i) = Char
      Case 32
        result(i) = Space
      Case 0 To 15
        result(i) = "%0" & Hex(CharCode)
      Case Else
        result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

This code make use of Microsoft’s XML library, so you have to add references to it. To add the reference go to Tools > References

You will see list of references with the check box, select the Microsoft XML v6.0, after that click Ok button.

Finally save your Visual basic window by pressing save button. Then from the drop down menu of Save as type select Excel Add-In, assign the file name of your desire. Once the file in saved in “*.xlam” extension you can open any new document and use the function. Close all the open excel files and open the new one.

3. Open the new excel file and then go to file > Options > Add-Inns. From the Manage drop down box select the Excel-Add Ins.

After selecting then press the GO button, this will list all the saved Add-ins files, as I have saved as Book1. Xlam I will check that box. Then press Ok after selecting,

4. As everything is done to perform the geocode, we are going to use the function to retrieve the longitude and latitude value. Let us suppose I have address 65 Hawthorn way, Olds, Alberta, Canada then you type that value in excel and use the function MyGeocode (as you type my it will show function on the box).

It will take few seconds to run the function because it has to pull the information from the google server and then result will display.

Now if you have number of addresses and you want to run the geocode then this function will help you. Just keep in mind few things while using this function, after the execution process is done then you can copy and paste the values because each time you open the file then it will processing each cells which might take up few minutes to perform it.

Second things as it is free they will not allow you to do perform thousands of geocode at once, so best way is to have a 10 seconds break between addresses. If Google stop you for retrieving large number of addresses, you will see error saying “Not Found (try again, you may have done too many too fast)”. See below example, I have tried few number of addressee without following 10 seconds rule.

I hope you liked this tutorial, please comment if you have any question regarding this.