How to Convert Latitude and Longitude to Address in Excel

Consider a List of Coordinates, which includes the Latitude and Longitude in columns B and C, respectively. We’ll convert these coordinates into human-readable addresses.

convert latitude and longitude to address in excel


Method 1 – Applying VBA Code for a User-Defined Function

Step 1 – Open the Microsoft Visual Basic for Applications IDE 

  • Go to the Developer tab.
  • Select Visual Basic in the Code group of commands.

Open Microsoft Visual Basic for Applications IDE

  • The Microsoft Visual Basic for Applications window pops up.
  • Go to the Insert tab.
  • Select Module from the available options.

Inserting Code Module

  • This will add a code module on the right side of the display.

Module inserted to write code to convert latitude and longitude to address in excel

Step 2 – Select the Right Reference 

  • Go to the Tools tab.
  • Click on References… from the options.

Select Right Reference

  • The References-VBAProject dialog box opens.
  • In the Available References section, check the box for Microsoft XML, v3.0.
  • Click OK.

Selecting Right Reference to convert latitude and longitude to address in excel

Step 3 – Build the Script

  • Copy the following code and paste it into the module.
Option Explicit
Function ReverseGeocode(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
ReverseGeocode = 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
ReverseGeocode = xD.XML
Else
Application.Caller.Font.ColorIndex = vbOK
ReverseGeocode = loca.Text
End If
End If
Exit Function
0:
Debug.Print Err.Description
End Function

Build the Script

  • Save the workbook as an Excel Macro-Enabled Workbook.

Saving workbook to convert latitude and longitude to address in excel

Step 4 – Use the User-Defined Function 

  • Construct a new column named Address under Column D.

Employ the User-Defined Function

  • Select cell D5 and insert the following in the cell.
=re
  • Select the ReverseGeocode function. Double-click on it or tap the Tab key to put it on the worksheet.

  • Provide the arguments of the function and press Enter.

Employing Function to convert latitude and longitude to address in excel

  • The first address is converted from the latitude and longitude in cells B5 and C5 and put in cell D5.

  • Bring the cursor to the bottom-right corner of cell D5 and it’ll look like a plus (+) sign. That’s the Fill Handle tool.
  • Double-click on it to copy the formula up to cell D14.

Using Fill Handle to convert latitude and longitude to address in excel

  • All the longitudes and latitudes have been converted to addresses.

Employing VBA Code to convert latitude and longitude to address in excel

  • You can compare the result with the following screenshot. It’s a picture of a website from where we get the latitudes and longitudes.


Method 2 – Using an Online Geocoding Tool

Steps:

  • Go to Geoapify.com

Geoapify website

  • We removed the heading from the previous dataset.
  • Save the file.

Using Online Geocoding Tool

  • Scroll down in your browser and you will get a place to input your dataset file.
  • Click on the “Browse Files” button.

Importing File

  • In the Open window, find the Excel file.
  • Click on Open.

  • We can see our file is inserted, and the website is showing a short preview of our dataset.

Preview of Dataset

  • Check the boxes for Latitude and Longitude which are our column names.
  • Choose lat for Latitude and lon for Longitude.

  • We selected English in the Language box.
  • Click on the Geocode button.

Selecting Language to convert latitude and longitude to address in excel

  • Click on Download geocoding results.

  • This will save a CSV file on the PC. Open the file to see the result.

Using Online tool to convert latitude and longitude to address in excel

Note: We’ve added some formatting to the CSV file for visual beautification. You can go through the article Formatting CSV File in Excel to learn more.


Download the Practice Workbook


<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

9 Comments
  1. Is it possible to convert this to post code only? I have 20,000 cases and it lags after I type it in 🙂

    Or maybe you know how to do this is in SQL?

    • Reply Avatar photo
      Shahriar Abrar Rafid Jun 22, 2023 at 2:46 PM

      Hello GREG,
      Thanks for your comment. Yes, you can do it. Here, I’m showing you to do it with Bing Maps API. Because it’s free. To use Google Maps API, you need to register with Card information. So, I have chosen Bing over Google here.
      The following VBA macro is the one-stop solution to your problem.

      Sub PostalCodeFromLatLong()
          Dim selectedRange As Range
          Dim latitudeColumn As Range
          Dim longitudeColumn As Range
          Dim postalCodeColumn As Range
          Dim apiKey As String
          Dim requestUrl As String
          Dim xmlhttp As Object
          Dim responseText As String
          Dim postalCode As String
          Dim i As Integer
          
          ' Select the range containing latitude and longitude coordinates
          On Error Resume Next
          Set selectedRange = Application.InputBox("Select the range containing latitude and longitude coordinates:", "ExcelDemy", Type:=8)
          On Error GoTo 0
          
          ' Check if a range is selected
          If selectedRange Is Nothing Then
              MsgBox "No range selected. Macro will exit.", vbExclamation, "ExcelDemy"
              Exit Sub
          End If
          
          ' Columns with latitude, longitude, and postal codes
          Set latitudeColumn = selectedRange.Columns(1)
          Set longitudeColumn = selectedRange.Columns(2)
          Set postalCodeColumn = selectedRange.Offset(0, 2).Resize(selectedRange.Rows.Count, 1)
          
          ' Bing Maps API Key
          apiKey = "Paste_your_Bing_Map_API_key_here"
          
          ' Loop through each row in the range
          For i = 1 To selectedRange.Rows.Count
              ' Get latitude and longitude values for the current row
              Dim latitude As Double
              Dim longitude As Double
              latitude = latitudeColumn.Cells(i).Value
              longitude = longitudeColumn.Cells(i).Value
              
              ' Construct the request URL
              requestUrl = "https://dev.virtualearth.net/REST/v1/Locations/" & latitude & "," & longitude & "?key=" & apiKey
              
              ' Create a new XMLHTTP object
              Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
              
              ' Send a GET request to the API
              xmlhttp.Open "GET", requestUrl, False
              xmlhttp.send
              
              ' Retrieve the response text
              responseText = xmlhttp.responseText
              
              ' Extract the postal code from the response
              postalCode = GetPostalCodeFromResponse(responseText)
              
              ' Write the postal code to the adjacent cell in the postal code column
              postalCodeColumn.Cells(i).Value = postalCode
          Next i
          
          MsgBox "Postal codes retrieved successfully!", vbInformation
      End Sub
      
      Function GetPostalCodeFromResponse(responseText As String) As String
          Dim postalCodeStartIndex As Long
          Dim postalCodeEndIndex As Long
          Dim postalCode As String
          
          ' Find the starting position of the postal code
          postalCodeStartIndex = InStr(responseText, """postalCode"":""") + Len("""postalCode"":""")
          
          ' Find the ending position of the postal code
          postalCodeEndIndex = InStr(postalCodeStartIndex, responseText, """") - 1
          
          ' Extract the postal code from the response
          postalCode = Mid(responseText, postalCodeStartIndex, postalCodeEndIndex - postalCodeStartIndex + 1)
          
          ' Return the postal code as a string with leading zeros
          GetPostalCodeFromResponse = "'" & postalCode
      End Function

      Run this macro and it will ask to input the range containing the latitude and longitude. Make sure to keep a blank column adjacent to the input columns. You’ll get the output there.

      Regards,
      SHAHRIAR ABRAR RAFID
      Team ExcelDemy

  2. Reply
    Kevin Joseph Wolfe Jan 19, 2024 at 7:45 AM

    What if you only want some of the parameters like Country, State, City, County, Zip Code. How much code would you need to change to get these?

    • Hi KEVIN,
      Thanks for reaching out to us. To get only the parameters you mentioned, adjust the XPath expressions used to select the specific elements from the XML response. Here’s the modified code:

      
      Option Explicit
      
      Function ReverseGeocode(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
              ' Handle parsing errors
              ReverseGeocode = "Error: " & xD.parseError.reason
          Else
              ' Set XPath selection language
              xD.SetProperty "SelectionLanguage", "XPath"
              
              ' Select specific elements using XPath
              Dim country As MSXML2.IXMLDOMElement
              Dim state As MSXML2.IXMLDOMElement
              Dim city As MSXML2.IXMLDOMElement
              Dim county As MSXML2.IXMLDOMElement
              Dim zipCode As MSXML2.IXMLDOMElement
              
              Set country = xD.SelectSingleNode("/reversegeocode/addressparts/country")
              Set state = xD.SelectSingleNode("/reversegeocode/addressparts/state")
              Set city = xD.SelectSingleNode("/reversegeocode/addressparts/city")
              Set county = xD.SelectSingleNode("/reversegeocode/addressparts/county")
              Set zipCode = xD.SelectSingleNode("/reversegeocode/addressparts/postcode")
              
              ' Build the result string in usual address manner
              ReverseGeocode = country.Text & ", " & state.Text & ", " & city.Text & ", " & _
                               county.Text & ", " & zipCode.Text
          End If
          Exit Function
      ErrorHandler:
          ' Handle runtime errors
          ReverseGeocode = "Error: " & Err.Description
      End Function
      

      Hope this helped. Let us know your further queries.
      Regards,
      Aung
      Team ExcelDemy

  3. what code would you change to get things other than postal code? Like State, City, or both?

  4. Thanks very much for this

  5. Hello! Thank you for your work!

    Only whole numbers work for me
    With GPS coordinates, look picture (line 1
    line 2)
    What can be the solution? Can you help me?

    Picture: https://www.kepfeltoltes.eu/images/2024/02/25/161error_gps.jpg

    • Reply Mahfuza Anika Era
      Mahfuza Anika Era Feb 27, 2024 at 11:56 AM

      Hi Zsolt!

      You are most welcome. It seems to me that you are trying to write a decimal number using a comma (,) instead of a decimal point(.). If you write the latitudes and longitudes as 42.328674 and -72.664658 instead of 42,328674 and -72,664658 the function should not return any error and you should get your expected result.

      null

      Regards,
      Nafis
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo