How to Convert ZIP Code to Latitude and Longitude in Excel

A ZIP (Zone Improvement Program) Code is a term used in the United States postal code system to define a specific location. Latitudes are horizontal lines that indicate how far north or south of the equator a location is. Longitudes are vertical lines that measure how far to the east or west of Greenwich, England’s meridian, a location is. Together, latitude and longitude allow cartographers, geographers, and others to pinpoint locations on Earth. Negative Latitude and Longitude values reflect the southern and western hemispheres respectively, while positive Latitudes and Longitudes indicate the northern and eastern hemispheres respectively.

In this article, we will demonstrate 2 methods to convert a Zip code to Latitude and Longitude coordinates in Excel using the below data set.

We used Microsoft Excel 365 here. The first Method requires Excel 365, but the second may work in earlier versions of Excel too.

Method 1 – Convert Zip Code to Latitude and Longitude Using Geography Data Type

Geography is a new data type accessible to Microsoft 365 users, which enables us to Convert ZIP Codes to Latitude and Longitude as well as access information such as population, time zone, region leaders, gas costs, and language. To input Geographic Data into Excel, the content must be converted to the Geography data type first.

STEPS:

• Select the range from B5 to B10.
• Navigate to the Data ribbon and then Geography.

• Select cell C5.
• Enter the formula below:

• Press the Tab key to view the result.

• Use the Fill Handle tool to drag the formula down to cell C10.

The Latitude coordinates are returned in column C.

• Select cell D5.
• Enter the formula below:

• Press Enter.

• Again, use the Fill Handle tool to drag the formula down to cell D10.

The Longitude coordinates are filled in column D.

Method 2 – Apply VBA Code to Convert a Zip Code to Latitude and Longitude

STEPS:

• Select the active sheet of the workbook.
• Go to the Developer tab.
• Click on Visual Basic.

• Select Insert and then Module to open the module box.
• Click the Tools menu item.
• Select References from the drop-down list.

• Check Microsoft XML, v3.0.
• Click OK.

• Enter the following code in the module box and click the Save icon:

Function findLatitude(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
        findLatitude = 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
            findLatitude = loc.getAttribute("lat")
        End If
    End If
End Function
Function findLongitude(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
        findLongitude = 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
            findLongitude = loc.getAttribute("lon")
        End If
    End If
End Function

• In cell C5 enter the formula below:

• Press Enter to view the result.

• Use the Fill Handle tool to copy the formula down to cell C10 below.

Latitude values are filled in column C.

• As before, select cell D5 and enter the formula below:

• Press Tab to display the result.

• Use the Fill Handle to copy the formula down to cell D10.

Longitude results are returned in column D.

Download Practice Workbook

<< Go Back to Geocoding in Excel | Learn Excel