How to Convert Address to Lat Long in Excel (2 Quick Ways)

If you’re working with location data in Excel, one common task is converting a list of addresses into latitude and longitude coordinates. This process is called geocoding, and it’s especially helpful for mapping, logistics, data analysis, and route optimization.

Luckily, you don’t need to rely on third-party tools or complex integrations to get it done.

In this article, we will show you how to convert addresses to latitude and longitude in Excel using two simple and free methods. One method uses the built-in Geography data type available in Microsoft Office 365, while the other relies on a custom VBA function that pulls data from OpenStreetMap with no paid tools or add-ins required.

 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
            Co_Ordinates = xDoc.XML
        Else
            Application.Caller.Font.ColorIndex = vbOK
            Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
        End If
    End If
End Function

For users with a Microsoft Office 365, Excel’s built-in Geography data type offers a straightforward way to obtain latitude and longitude information without the need for external APIs or add-ins.

This feature works by recognizing the city or region within the address and displaying its general coordinates. Keep in mind that it may return the latitude and longitude of the city center rather than the precise street-level location.

Steps:

➤ Enter your list of addresses in a single column.

➤ Select the cells containing the addresses.
➤ Navigate to the Data tab on the ribbon.

➤ Click on Geography in the Data Types group.

➤ Once Excel recognizes the data type, a small icon will appear next to each address.

➤ Click on the icon, then select Latitude and Longitude from the available fields to insert them into adjacent columns.

This method provides a quick and efficient way to retrieve geographical coordinates directly within Excel.

When you want to get latitude and longitude for an address directly within Excel, you can embed a VBA function that calls an API to convert addresses into coordinates. Let’s walk through the steps to set this up.

This is the dataset that we will be using:

Steps:

➤ Enter  Alt  +  F11  to open VBA.
➤ Go to Tools and select References.

➤ In the References box, check Microsoft XML, v3.0 and press OK.

➤ Click on Insert and select Module.

➤ A blank module will appear.
➤ Copy the code below and paste it into the module:

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
            Co_Ordinates = xDoc.XML
        Else
            Application.Caller.Font.ColorIndex = vbOK
            Co_Ordinates = loc.getAttribute("lat") & "," & loc.getAttribute("lon")
        End If
    End If
End Function

➤ Press the Save icon in the console.

➤ Close the Visual Basic console.
➤ Back in your Excel dataset, select cell B2.
➤ Type in this formula to get the latitude and longitude for the address in B2:

➤ Press Enter.

➤ Drag the AutoFill handle down to apply the formula to the rest of the column.

Frequently Asked Questions

Can I use these methods in Excel 2016 or earlier?

The Geography data type is only available in Microsoft Office 365. For Excel 2016 or earlier, the VBA method using OpenStreetMap will still work.

Is an internet connection required?

Yes. Both the Geography feature and the VBA geocoding function require internet access to fetch location data from online sources.

Are these methods free?

Yes. Both options are completely free. Excel’s Geography feature is included with Microsoft Office 365, and the OpenStreetMap API used in the VBA method is open-source and public.

What if my address is not recognized?

This usually happens if the address is incomplete or not formatted correctly. Try simplifying or standardizing the address. In VBA, if the result is blank, the API may not have matched it properly.

Is the VBA method safe to use?

Yes, as long as you copy the code correctly and use it only for your internal datasets. It sends a basic request to OpenStreetMap’s public geocoding service and does not store or expose your data externally.

Wrapping Up

In this tutorial, we learned how to convert addresses to latitude and longitude in Excel using two effective methods: the built-in Geography data type for Microsoft Office 365 users and a simple VBA script that fetches coordinates from OpenStreetMap. Both approaches allow you to perform location lookups right inside your spreadsheet. Feel free to download the practice file and share your thoughts and suggestions.