How to Convert Latitude and Longitude to Address in Excel

Get FREE Advanced Excel Exercises with Solutions!

If you have the latitude and longitude of a particular place, you can find out the address of this place using Excel. OMG!! So cool, isn’t it? In this article, we’ll demonstrate 2 easy and quick methods to convert latitude and longitude to address in Excel. Moreover, the pictures are so beautiful that you can understand the whole matter just by looking at them. So, let’s go through the whole article to learn this and implement it in your work life.


Convert Latitude and Longitude to Address in Excel: 2 Methods

For ease of understanding, we are going to use a List of Coordinates. This dataset includes the Latitude and Longitude in columns B and C respectively.

convert latitude and longitude to address in excel

Now, we’ll convert these coordinates into human-readable addresses. To do this we’ll use 2 different approaches. So, let’s explore them one by one.
Here, we have used Microsoft Excel 365 version, you may use any other version according to your convenience.


1. Applying VBA Code

In our first method, we’ll apply the VBA script to our worksheet. Using the VBA code, we’ll create a user-defined function first. Then, we’ll apply this function to convert the coordinates into addresses. So, let’s follow the procedure step-by-step.

Step 01: Open Microsoft Visual Basic for Applications IDE 

  • At the very beginning, go to the Developer tab.
  • Then, select Visual Basic on the Code group of commands.

Open Microsoft Visual Basic for Applications IDE

Immediately, the Microsoft Visual Basic for Applications window appears before us.

  • Here, proceed to the Insert tab.
  • After that, select Module from the available options.

Inserting Code Module

Instantly, it 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 02: Select Right Reference 

  • Now, advance to the Tools tab.
  • Then, click on References… from the options.

Select Right Reference

Suddenly, the References-VBAProject dialog box opens.

  • In the Available References section, check the box of Microsoft XML, v3.0.
  • As usual, click OK.

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

Step 03: Build the Script

  • At this time, 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

  • Following this, Save the workbook till this condition.

Saving workbook to convert latitude and longitude to address in excel

Note: While saving, make sure to save it as an Excel Macro-Enabled Workbook.

Step 04: Employ the User-Defined Function 

Currently, we’ll employ the code in our worksheet.

  • Firstly, construct a new column named Address under Column D.

Employ the User-Defined Function

  • Secondly, select cell D5 and start writing the following in the cell.
=re

Immediately, you can see the suggestions for functions.

  • From the suggestions, select the ReverseGeocode function which we created a while ago. Double-click on it or tap the TAB key to employ it on the worksheet.

  • Then, give the arguments of the function and press ENTER.

Employing Function to convert latitude and longitude to address in excel

Finally, the first address converted from the latitude and longitude in cells B5 and C5 is in cell D5.

  • Presently, bring the cursor to the right-bottom corner of cell D5 and it’ll look like a plus (+) sign. Actually, it’s the Fill Handle tool.
  • Now, double-click on it to copy the formula up to cell D14.

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

Lastly, all the longitudes and latitudes have been converted to addresses in our Excel worksheet.

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.


2. Using Online Geocoding Tool

This approach is quite easy. You don’t have to create any macro or function on your own. You just have to import the coordinates to an online website Geoapify and it will do the rest. So, without further delay, let’s dive in!

📌 Steps:

  • At first, click on the above link of the website.

Geoapify website

  • Here, we have prepared the dataset. It’s the same dataset that we used in the previous method. But the difference is we removed the heading in this for the convenience of this tool.

Using Online Geocoding Tool

  • Primarily, scroll down a bit and you will get a place to input your dataset file.
  • Here, click on the “Browse Files” button.

Importing File

  • In the Open window, select Desktop and choose the desired file.
  • After that, click on Open.

We can see our file is inserted already. And the website is showing a short preview of our dataset.

Preview of Dataset

  • Again, scroll for a while.
  • Now, check the boxes of Latitude and Longitude which are our column names.
  • Following this, choose lat for Latitude and lon for Longitude.

  • Then, we selected English in the Language box.
  • Lastly, click on the Geocode button.

Selecting Language to convert latitude and longitude to address in excel

It shows that all the locations have been geocoded. That means we can get addresses from those coordinates.

  • Next, click on Download geocoding results.

Suddenly, it’ll save a CSV file on our desktop.

  • So, 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 know more about this.


Download Practice Workbook

You may download the following Excel workbooks for better understanding and practice yourself.


Conclusion

This article explains how to convert latitude and longitude to address in Excel in a simple and concise manner. Don’t forget to download the Practice file. Thank you for reading this article. We hope this was helpful. Please let us know in the comment section if you have any queries or suggestions.


<< Go Back to Geocoding in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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