Yousuf Khan
In this article, we will learn how to convert an address to latitude and longitude in Microsoft Excel. In detail, Excel provides some new cool features to extract coordinates of any location in a minute. So, to summarize, let’s see these 2 easy methods to convert Address to Lat–Long in Excel for your articles and workbooks.
Download Practice Workbook
You can download this workbook to practice yourself.
2 Easy Methods to Convert Address to Lat Long in Excel
We’ll use a new Excel data type, Geography introduced in Microsoft Excel 365 and Excel VBA code in these 2 methods. To demonstrate, we take a dataset where column B consists of various addresses. And, column C refers to the latitude-longitude of those locations. For better understanding, we use the degrees & decimal minutes system to indicate the coordinates. In this system, we will assign positive & negative values to the coordinates. For instance, a positive latitude indicates it falls north of the equator whereas a negative latitude locates south. Similarly, positive & negative longitude indicate the east & west of the Prime meridian respectively. Meanwhile, for this purpose of demonstration, I will use Microsoft Excel 365. I used the sample dataset listed below.
1. Transform Address to Lat Long in Excel Through Geography Data Type
Firstly, this method will show you how to convert an address to its latitude and longitude using a new data type. As shown below, we have a list of address data from different cities all over the world. Provided that, we will use the Geography feature to illustrate this method. The Geography data type converts the city name data into the Geography data type. Geography data type gives out details like population, area, languages, and many more as long as you are connected to the internet. Moreover, this in-built feature provides the latitude and longitude of a specific area. Let’s follow the procedure.
Steps:
- First, Select column B5:B12.
- Then, press the Data tab.
- Next, tap Geography as marked below.
- The Geography tool will convert your data into geography data type as each data gets a Map icon as shown below.
- Now, in cell C5, type the following formula:
=B5.Latitude- Also, press enter to get the output.
- Next, use the AutoFill tool.
- Hence, you get the latitudes.
- Similarly, we will try to extract the longitude also.
- In this case, type this formula:
=B5.Longitude
- Finally, press enter to get the result.
- But, this output will return the column as a formula cell range. We will need these columns as latitude & longitude datatypes. So, we need to follow one or two steps more.
- First, select columns C & D.
- Then, press Copy in the Home tab.
- Now, we need to Paste them into the dataset as Values & Number Formatting (A).
- Click the Dropdown icon under Paste.
- And finally, paste it as Values & Number Formatting (A) in the Paste Values.
- Hence, the desired result appears.
Read More: How to Create Latitude Longitude Converter in Excel (2 Examples)
2. Embed VBA Code to Convert Address to Lat Long in Excel
Secondly, in this method, we will try to extract the latitude & longitude of various addresses using Excel VBA Code. Follow these easy processes to do so.
Steps:
- Firstly, go to the Developer tab.
- After that, click the Visual Basic window.
- Eventually, a console will appear.
- Here, tap Insert and then consecutively press Module.
- As a result, a module will appear.
- Before going into the code section, we need to do a critical task.
- Go to Tools and then select References as shown below.
- After a while, the References box will appear.
- Here, scroll down till Microsoft XML, v3.0Â appear.
- Now, check the box, and at last press OK.
- Next, in this part, copy the Excel VBA Code and paste it into the Module.
- You can copy the code given below:
Function Co_Ordinates(address As String) As String
   Application.Caller.Font.ColorIndex = xlNone
   Dim xDoc As New MSXML2.DOMDocument
   xDoc.async = False
   xDoc.Load ("https://nominatim.openstreetmap.org/search?format=xml&q=" + WorksheetFunction.EncodeURL(address))
   If xDoc.parseError.ErrorCode <> 0 Then
       Application.Caller.Font.ColorIndex = vbErr
       Co_Ordinates = xDoc.parseError.reason
   Else
       xDoc.SetProperty "SelectionLanguage", "XPath"
       Dim loc As MSXML2.IXMLDOMElement
       Set loc = xDoc.SelectSingleNode("/searchresults/place")
       If loc Is Nothing Then
           Application.Caller.Font.ColorIndex = vbErr
           NominatimGeocode = xDoc.XML
       Else
           Application.Caller.Font.ColorIndex = vbOK
           Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
       End If
   End If
End Function
- However, we need to save this code as Excel Macro-Enabled Workbook type.
- Subsequently, press the Save icon.
- Eventually, it will return a Save As console.
- Afterward, open the Save as type bar to explore the types.
- Finally, select the Excel Macro-Enabled Workbook type and save it.
- After saving the code file, close the tab.
- Next, go to your dataset and select cell C5.
- Here, we type another formula:
=Co_Ordinates(B5)
- Lastly, press Enter.
- Use the AutoFill tool.
- Hence, the required latitudes & longitudes will pop up.
Read More: How to Convert Latitude and Longitude to Address in Excel
Conclusion
In conclusion, we have discussed here some easy ways to create an Excel zip code formula. Not to mention, our ExcelDemy website shows various simple problem-solving methods like this. Please feel free to leave any further queries or recommendations in the comment box below.
