How to Perform Reverse Geocoding in Excel: 5 Methods

Method 1 – Open Microsoft Visual Basic Application from Developer Tab

  • Go to the Developer tab of the ribbon
  • From the Code group, select Visual Basic.

Opening Microsoft Visual Basic Application from Developer Tab as An Easy Step to Perform Reverse Geocoding in Excel

  • You will see the Visual Basic window.
  • From the Insert tab, choose Module.

  • A module will look like the following image after opening, where you can write the code.


Method 2 – Select Necessary Reference from Tool Option

  • From the Tools tab of the VBA window, select References.

  • You will see a list of available references in the References – VBAProject dialog box.
  • Select Microsoft XML, v3.0, and make sure the reference is marked.
  • Press OK.

Selecting Necessary Reference from Tool as An Easy Step to Perform Reverse Geocoding in Excel


Method 3 – Use Code to Build Required Custom Function

  • Copy the following code and paste it into the module from the first step.
  • We named the custom functions as ReverseGecoder.
Option Explicit
Function ReverseGeocoder(lati As Double, longi As Double) As String
On Error GoTo 0
Dim xD As New MSXML2.DOMDocument
Dim URL As String, vbErr As String
xD.async = False
URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
"&lon=" + CStr(longi)
xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
"&lon=" + CStr(longi))
If xD.parseError.ErrorCode <> 0 Then
Application.Caller.Font.ColorIndex = vbErr
ReverseGeocoder = xD.parseError.reason
Else
xD.SetProperty "SelectionLanguage", "XPath"
Dim loca As MSXML2.IXMLDOMElement
Set loca = xD.SelectSingleNode(" / reversegeocode / result")
If loca Is Nothing Then
Application.Caller.Font.ColorIndex = vbErr
ReverseGeocoder = xD.XML
Else
Application.Caller.Font.ColorIndex = vbOK
ReverseGeocoder = loca.Text
End If
End If
Exit Function
0:
Debug.Print Err.Description
End Function

Writing Code to Build Required Custom Function as An Easy Step to Perform Reverse Geocoding in Excel

  • After pasting the code save it, and go back to the worksheet with the coordinates.


Method 4 – Implement Custom Function

  • In your Excel sheet, type =REV in cell D5 and you will see the auto-suggestion of the custom function from the previous step.
  • Select the function press Tab on the keyboard or double-click on the function using the mouse.

  • Implement the function type the following formula in cell D5.
=ReverseGeocoder(B5,C5)

  • Press Enter and see the desired location for the coordinates of cells B5 and C5.


Method 5 – Show Final Result

  • The AutoFill feature in the following image is if you take your cursor at the lower right corner of cell D5.
  • Drag the feature to the lower to implement the formula in the lower cells as well.

  • You will be able to see all the locations by using the custom functions which work as a reverse geocoder.


Things to Remember

  • If you don’t activate the reference before implementing the custom function, it won’t show the desired result.
  • Your Excel file might act slower after implementing the function, so keep patience after using AutoFill.

Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

12 Comments
  1. Hi Araf, thank you for sharing Knoledge.

    is it possible to get only the city name? what should i change in the code?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Feb 5, 2024 at 3:02 PM

      Hello KHALED

      Thanks for your nice words. Your appreciation means a lot to us. You only want to get the city name from lat and long values.

      I am delighted to inform you that I have developed another VBA User-defined function named ExtractCityNames that will extract the city names by taking input from the User-defined function mentioned in this article.

      Follow these steps:

      1. Press Alt+F11.

      2. Click on Insert followed by Module.

      3. Paste the following code in the Module and save it.

      
      Function ExtractCityNames(inputTextFromUDF As String) As String
          
          Dim inputText As String
          Dim splitText() As String
          Dim cityName As String
          Dim i As Integer
          
          inputText = inputTextFromUDF
          
          splitText = Split(inputText, vbCrLf)
          
          For i = 0 To UBound(splitText)
              splitText(i) = Trim(splitText(i))
              Dim parts() As String
              parts = Split(splitText(i), ",")
              
              If IsNumeric(Trim(parts(UBound(parts) - 1))) Then
                  cityName = Trim(parts(UBound(parts) - 2))
              Else
                  cityName = Trim(parts(UBound(parts) - 1))
              End If
              
              ExtractCityNames = ExtractCityNames & cityName & vbCrLf
          Next i
      
      End Function
      
      Function ReverseGeocoder(lati As Double, longi As Double) As String
      
          On Error GoTo 0
          Dim xD As New MSXML2.DOMDocument
          Dim URL As String, vbErr As String
          xD.async = False
          URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
          "&lon=" + CStr(longi)
          xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
          "&lon=" + CStr(longi))
          If xD.parseError.ErrorCode <> 0 Then
              Application.Caller.Font.ColorIndex = vbErr
              ReverseGeocoder = xD.parseError.reason
          Else
              xD.SetProperty "SelectionLanguage", "XPath"
              Dim loca As MSXML2.IXMLDOMElement
              Set loca = xD.SelectSingleNode(" / reversegeocode / result")
              If loca Is Nothing Then
                  Application.Caller.Font.ColorIndex = vbErr
                  ReverseGeocoder = xD.XML
              Else
                  Application.Caller.Font.ColorIndex = vbOK
                  ReverseGeocoder = loca.Text
              End If
          End If
          Exit Function
      0:
          Debug.Print Err.Description
      
      End Function
      

      4. Return to the sheet and select cell D3.

      5. Insert the following formula and hit Enter.

      =ExtractCityNames(ReverseGeocoder(B3,C3))

      6. Finally, copy the formula down using the Fill Handle icon.

      Things to Remember: The return time can be longer than usual as we use complex Excel VBA User-defined functions.

      I am attaching the solution workbook for better understanding. Hopefully, the idea will fulfil your goal. Stay Blessed.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  2. Great Job, thanks!

    I would like to have all information in different fields, e.E. Country, Street, House number, etc. Especially I need the Country values. Any idea?

    Peter

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 18, 2024 at 1:45 PM

      Hello Peter Berger

      Thanks for your nice words. Your appreciation means a lot to us.

      You wanted to get all the address information in different columns. To do so, I have developed another assistive VBA user-defined function and improved the existing user-defined function named ReverseGeocoder. Using these two functions, you can quickly fulfil your goal.

      Follow these steps:

      1. Press Alt+F11.
      2. Click on Insert followed by Module.
      3. Paste the following improved code in the module and save it:
        Option Explicit
        
        Function ReverseGeocoder(lati As Double, longi As Double) As Variant
            On Error GoTo 0
            
            Dim xD As New MSXML2.DOMDocument
            Dim URL As String, vbErr As String
            
            xD.async = False
            URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                    "&lon=" + CStr(longi)
            
            xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                    "&lon=" + CStr(longi))
            
            If xD.parseError.ErrorCode <> 0 Then
                Application.Caller.Font.ColorIndex = vbErr
                ReverseGeocoder = xD.parseError.reason
            Else
                xD.SetProperty "SelectionLanguage", "XPath"
                Dim loca As MSXML2.IXMLDOMElement
                Set loca = xD.SelectSingleNode("/reversegeocode/result")
                
                If loca Is Nothing Then
                    Application.Caller.Font.ColorIndex = vbErr
                    ReverseGeocoder = xD.XML
                Else
                    Application.Caller.Font.ColorIndex = vbOK
                    ReverseGeocoder = SplitStringToArray(loca.Text)
                    
                End If
            End If
            
            Exit Function
            
        0:
            Debug.Print Err.Description
            
        End Function
        
        Function SplitStringToArray(strInput As String) As Variant
            
            Dim arrResult As Variant
            Dim i As Integer
            
            arrResult = Split(strInput, ", ")
            
            For i = LBound(arrResult) To (UBound(arrResult) - 1) / 2
                Dim temp As String
                temp = arrResult(i)
                arrResult(i) = arrResult(UBound(arrResult) - i)
                arrResult(UBound(arrResult) - i) = temp
            Next i
            
            If Not IsNumeric(arrResult(1)) Then
        
                ReDim Preserve arrResult(UBound(arrResult) + 1)
                For i = UBound(arrResult) - 1 To 1 Step -1
                    arrResult(i + 1) = arrResult(i)
                Next i
        
                arrResult(1) = ""
            
            End If
            
            SplitStringToArray = arrResult
        
        End Function

      4. Return to the sheet and select the intended cell.
      5. Insert the following formula: =ReverseGeocoder(B5,C5)
      6. Hit Enter to see an output like the following GIF.
      7. Now, drag the Fill Handle icon to copy the formula down.

      I hope you have found the VBA user-defined function helpful. I have attached the solution workbook; good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  3. The extraction of cities work not well:

    The identified city here is not “Marmara Bölgesi”, it’s Istanbul!
    41.03 28.96 Kadir Has Caddesi, Küçükmustafapaşa, Yavuz Sultan Selim Mahallesi, İstanbul, Fatih, İstanbul, Marmara Bölgesi, 34083, Türkiye “Marmara Bölgesi”

    The identified city here is not “France métropolitaine”, it’s Troyes!
    48.30 4.08 55, Rue de la Cité, Quartier de la Cité, Troyes, Aube, Grand Est, France métropolitaine, 10000, France “France métropolitaine”

    Additional to my last message it would better to take the nominatim results and use the field like in the second block, the block of the answer, for single fields in Excel. Then no errors would be possible.

    Example of an answer from nominatim:

    25b, Bauherrenstrasse, Höngg, Kreis 10, Zurich, District Zurich, Zurich, 8049, Switzerland

    25b
    Bauherrenstrasse
    Höngg
    Zurich
    District Zurich
    Zurich
    CH-ZH
    8049
    Switzerland
    ch

    Kind regards

    Peter

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Apr 18, 2024 at 2:51 PM

      Dear Peter Berger

      Thanks for your feedback. Perhaps you are right about using the direct results from Nominatim, which would lead to more accurate outcomes. However, we have to get the result from the response from the Nominatim API.

      In addition to your previous post, you wanted to get all the information in a column but in different cells. Do not worry! I have improved the previously given code by using a 2D array.

      SOLUTION Overview:

      Required Excel VBA User-Defined Functions:

      Option Explicit
      
      Function ReverseGeocoder(lati As Double, longi As Double) As Variant
          On Error GoTo 0
          
          Dim xD As New MSXML2.DOMDocument
          Dim URL As String, vbErr As String
          
          xD.async = False
          URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                  "&lon=" + CStr(longi)
          
          xD.Load ("https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                  "&lon=" + CStr(longi))
          
          If xD.parseError.ErrorCode <> 0 Then
              Application.Caller.Font.ColorIndex = vbErr
              ReverseGeocoder = xD.parseError.reason
          Else
              xD.SetProperty "SelectionLanguage", "XPath"
              Dim loca As MSXML2.IXMLDOMElement
              Set loca = xD.SelectSingleNode("/reversegeocode/result")
              
              If loca Is Nothing Then
                  Application.Caller.Font.ColorIndex = vbErr
                  ReverseGeocoder = xD.XML
              Else
                  Application.Caller.Font.ColorIndex = vbOK
                  ReverseGeocoder = SplitStringToArray(loca.Text)
              End If
          End If
          
          Exit Function
          
      0:
          Debug.Print Err.Description
          
      End Function
      
      Function SplitStringToArray(strInput As String) As Variant
          
          Dim arrResult As Variant
          Dim i As Integer
          
          arrResult = Split(strInput, ", ")
          
          Dim numRows As Long
          numRows = UBound(arrResult) - LBound(arrResult) + 1
          
          Dim result() As Variant
          ReDim result(1 To numRows, 1 To 1)
          
          For i = LBound(arrResult) To UBound(arrResult)
              result(i + 1, 1) = arrResult(i)
          Next i
          
          SplitStringToArray = result
      
      End Function

      Hopefully, you have found the idea helpful. Download the solution workbook. Stay blessed.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  4. Thanks a lot for these codes. They worked pretty well but on a few set of coordinates.

    I have over 5,000 sets of coordinates and am wondering if there is anything that can reverse geocode these in minutes?

    Regards,
    Olaide

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 2, 2024 at 12:28 PM

      Hello Olaide

      Thanks for thanks! You are very welcome. Your appreciation means a lot to us.

      You are right! The user-defined function works perfectly fine for a few sets of coordinates. I have reviewed the code and estimated the time required to perform it on a pair of coordinates; it takes 5.7 seconds on my machine (the time can vary from device). It will take almost 8 hours to perform 5000 sets of coordinates with a spontaneous internet connection.

      The user-defined function mentioned in this article uses the OpenStreetMap Nominatim API to perform reverse geocoding. It retrieves location information based on the lat and long values given to it. It must take the required time to perform perfectly.

      So, it is impossible to reverse geocode 5000 sets of coordinates within minutes. If you ever feel like getting location information without applying the user-defined function like the regular Excel function, you can use the following sub-procedure. You need to run the code, and it will consider the lat and long values as columns B and C starting from row 3 and display the result in column D.

      Sub PopulateReverseGeocoding()
      
          Dim ws As Worksheet
          Dim lastRow As Long
          Dim i As Long
          
          Set ws = ThisWorkbook.Sheets("Sheet1")
          
          lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row
          
          For i = 3 To lastRow
          
              Dim lat As Double
              Dim lon As Double
              lat = ws.Cells(i, "B").Value
              lon = ws.Cells(i, "C").Value
              
              ws.Cells(i, "D").Value = ReverseGeocoder(lat, lon)
          
          Next i
      
      End Sub
      
      Function ReverseGeocoder(lati As Double, longi As Double) As String
          On Error GoTo ErrorHandler
          
          Dim xD As New MSXML2.DOMDocument
          Dim URL As String
          
          xD.async = False
          URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + _
                "&lon=" + CStr(longi)
          
          xD.Load (URL)
          
          If xD.parseError.ErrorCode <> 0 Then
              ReverseGeocoder = xD.parseError.reason
          Else
              xD.SetProperty "SelectionLanguage", "XPath"
              Dim loca As MSXML2.IXMLDOMElement
              Set loca = xD.SelectSingleNode("/reversegeocode/result")
              
              If loca Is Nothing Then
                  ReverseGeocoder = "No location found."
              Else
                  ReverseGeocoder = loca.Text
              End If
          End If
          
          Exit Function
          
      ErrorHandler:
          ReverseGeocoder = "Error occurred: " & Err.Description
          
      End Function

      I hope you understand the situation. Stay blessed.

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

  5. I’m glad I came back here! it didn’t work for me before but I see that you updated it. I’m going to give it a shot! fingers crossed.

    • Hello Jen,

      Thanks for coming back, hopefully it will work this time. If it doesn’t work, you can let us know your problem in the comment section below.

      Regards
      ExcelDemy

  6. SO YAYYY IT WORKS BUT… Can I get the code with less information and would that require less RAM to perform? Like I dont need the county or the town… Just the physical address like house number, road, city, state and zip code? Thanks again!

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 16, 2024 at 1:23 PM

      Dear Jen

      Thanks for explaining your requirements clearly. I have reviewed the existing user-defined function and tried to improve it to achieve your goal. The improved user-defined function will try to populate house numbers, roads, cities, states and zip codes by taking Lat and Long values; if any item is missing, it will display an extension like Incomplete!

      Follow these steps:

      1. Click on the Developer tab, followed by Visual Basic.
      2. Next, click on Insert, followed by Module.
      3. Paste the following code in the module and save it:
        Option Explicit
        
        Function ReverseGeocoder(lati As Double, longi As Double) As String
        
            On Error GoTo ErrorHandler
            Dim xD As New MSXML2.DOMDocument
            Dim URL As String
            Dim house As String
            Dim road As String
            Dim city As String
            Dim state As String
            Dim postcode As String
            
            xD.async = False
            URL = "https://nominatim.openstreetmap.org/reverse?lat=" + CStr(lati) + "&lon=" + CStr(longi)
            xD.Load (URL)
            
            If xD.parseError.ErrorCode <> 0 Then
                ReverseGeocoder = "Error: " & xD.parseError.reason
            Else
                xD.SetProperty "SelectionLanguage", "XPath"
                
                Dim houseNode As MSXML2.IXMLDOMElement
                Dim roadNode As MSXML2.IXMLDOMElement
                Dim cityNode As MSXML2.IXMLDOMElement
                Dim stateNode As MSXML2.IXMLDOMElement
                Dim postcodeNode As MSXML2.IXMLDOMElement
                
                Set houseNode = xD.SelectSingleNode("//addressparts/house_number")
                Set roadNode = xD.SelectSingleNode("//addressparts/road")
                Set cityNode = xD.SelectSingleNode("//addressparts/city")
                Set stateNode = xD.SelectSingleNode("//addressparts/state")
                Set postcodeNode = xD.SelectSingleNode("//addressparts/postcode")
                
                If Not houseNode Is Nothing Then
                    house = houseNode.Text
                End If
                
                If Not roadNode Is Nothing Then
                    road = roadNode.Text
                End If
                
                If Not cityNode Is Nothing Then
                    city = cityNode.Text
                End If
                
                If Not stateNode Is Nothing Then
                    state = stateNode.Text
                End If
                
                If Not postcodeNode Is Nothing Then
                    postcode = postcodeNode.Text
                End If
                
                If house = "" Or road = "" Or city = "" Or state = "" Or postcode = "" Then
                    ReverseGeocoder = house & " " & road & ", " & city & ", " & state & ", " & postcode & " [Incomplete!]"
                Else
                    ReverseGeocoder = house & " " & road & ", " & city & ", " & state & ", " & postcode
                End If
            End If
            
            Exit Function
        
        ErrorHandler:
            ReverseGeocoder = "Error: " & Err.Description
            
        End Function

      4. Return to the sheet and choose an empty cell.
      5. Apply the following formula: =ReverseGeocoder(B5,C5)
      6. Drag the Fill Handle icon to copy the formula down.

      I hope you have found the user-defined function of reverse geocoding you were looking for. I have attached the solution workbook as well. Good luck.

      DOWNLOAD SOLUTION WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      Excel & VBA Developer
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo