How to Convert Address to Lat Long in Excel (2 Easy Methods)

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.

Convert Address to Lat Long in Excel: 2 Easy Methods

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:

• 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:

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

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 appears.
• 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:

• Lastly, press Enter.
• Use the AutoFill tool.
• Hence, the required latitudes & longitudes will pop up.

Download Practice Workbook

You can download this workbook to practice yourself.

Conclusion

In conclusion, we have discussed here some easy ways to create an Excel zip code formula. Please feel free to leave any further queries or recommendations in the comment box below.

<< Go Back to Geocoding in Excel | Learn Excel