How to Perform Reverse Geocoding in Excel (with Easy Steps)

Reverse geocoding means finding out the actual address or location from a given latitude and longitude. This task can be quite hectic if you want to perform it manually. But with the help of Excel, you can perform reverse geocoding easily. In this article, I will show you, how to perform reverse geocoding in Excel.


Reverse Geocoding in Excel: 5 Easy Steps

In this article, you will see five easy steps to perform reverse geocoding in Excel. To perform this task, I will need the help of VBA code to build a custom function for Excel. Then, by applying the custom function, I will find out the address from the latitude and longitude.

To illustrate my article further, I will use the following sample data set.

5 Easy Steps to Perform Reverse Geocoding in Excel


Step 1: Open Microsoft Visual Basic Application from Developer Tab

Firstly, to write a VBA code, you must open a module, as you cannot write it into a normal worksheet like other formulas. For that,

  • First of all, go to the Developer tab of the ribbon
  • Then 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

  • Secondly, you will see the Visual Basic window.
  • Afterward, from the Insert tab, choose Module.

  • Consequently, a module will look like the following image after opening where you will be able to write the code.


Step 2: Select Necessary Reference from Tool Option

In the second step, you will need to activate a particular reference so that the custom function can perform accurately. To do that,

  • Firstly, from the Tools tab of the VBA window, select References.

  • Secondly, you will see a list of available references in the References – VBAProject dialog box.
  • Then, from the list, select Microsoft XML, v3.0, and make sure the reference is marked.
  • Lastly, press OK.

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


Step 3: Use Code to Build Required Custom Function

Now for the third step, I will write the code that will create the custom function required for performing reverse geocoding. For that,

  • First of all, copy the following code and paste it into the module from the first step.
  • Here, I have 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

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


Step 4: Implement Custom Function

In the fourth step, I will implement the custom function from the previous procedure and see if it really shows the address for a particular coordinate. To do that,

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

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

  • Finally, press Enter and see the desired location for the coordinates of cells B5 and C5.


Step 5: Show Final Result

This is the final step of this procedure. After completing all the previous steps, I will now show the final result or outcome. To do that,

  • First of all, you will find the AutoFill feature in the following image if you take your cursor at the lower right corner of cell D5.
  • After selecting it drag the feature to the lower to implement the formula in the lower cells as well.

  • Finally, 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.


Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to perform reverse geocoding in Excel. Please share any further queries or recommendations with us in the comments section below.

We are always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.


<< 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.

6 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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo