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.
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.
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.
Instantly, it will add a code module on the right side of the display.
Step 02: Select Right ReferenceÂ
- Now, advance to the Tools tab.
- Then, click on References… from the options.
Suddenly, the References-VBAProject dialog box opens.
- In the Available References section, check the box of Microsoft XML, v3.0.
- As usual, click OK.
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
- Following this, Save the workbook till this condition.
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.
- 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.
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.
Lastly, all the longitudes and latitudes have been converted to addresses in our Excel worksheet.
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.
- 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.
- Primarily, scroll down a bit and you will get a place to input your dataset file.
- Here, click on the “Browse Files” button.
- 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.
- 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.
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.
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.
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
Get FREE Advanced Excel Exercises with Solutions!
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?
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.
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
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:
Hope this helped. Let us know your further queries.
Regards,
Aung
Team ExcelDemy
what code would you change to get things other than postal code? Like State, City, or both?
Thanks very much for this
Hello Loni,
You are most welcome.
Regards
ExcelDemy
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
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.
Regards,
Nafis
ExcelDemy