How to Convert ZIP Code to Latitude and Longitude in Excel

Microsoft Excel is unquestionably one of the most vital, robust computer programs now accessible. Using Excel’s features and resources, we can conduct an infinite range of calculations on any dataset. This article will cover two basic Excel methods for transforming zip codes to latitude and longitude. Therefore, you should use these 2 simple methods to Convert Zip Code to Latitude and Longitude in Excel.

Convert Zip Code to Latitude and Longitude in Excel: 2 Easy Ways

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

As an illustration, the below data set has a column of Zip Code values and two columns of Latitude and Longitude values. Using Google Earth, we obtain these figures for certain American states. Using these two methods, we’ll convert ZIP Codes to Latitude and Longitude in Excel. Also, I have yet to mention that I’ve been writing this article using Microsoft Excel 365.

1. Convert Zip Code to Latitude and Longitude Through Geography Data Type

Geography is a new data type accessible to Microsoft 365 customers. Using the Geography data type, you may access information such as population, time zone, region leaders, gas costs, and language. To input Geographic Data into Excel, convert the content to the Geography data type first. This feature allows us to Convert ZIP Codes to Latitude and Longitude in Excel. Follow the instructions below to complete the assignment.

STEPS:

• First, select the range from B5 to B10.
• Second, navigate to the Data ribbon and then Geography.

• Thirdly, select cell C5.
• Now, put the formula below:

• After that, press the Tab key to view the answer in cell C5.

• Furthermore, tap the Fill Handle icon.
• Essential, hold, and drag to cell C10.
• As a result, it will return the desired output as below.

• Next, select cell D5.
• At this point, input the formula below:

• Later, press Enter to display the response in cell D5.

• Again, tap the Fill Handle icon.
• Not only hold but also drag to cell D10.
• Consequently, it will provide the intended outcome, as seen below.

2. Apply Excel VBA to Convert the Zip Code to Latitude Longitude

Alternatively, we may use Excel VBA to convert Zip Codes to Latitude and Longitude. Please follow the instructions below to complete the task efficiently. We’ll use the same dataset.

STEPS:

• Firstly, choose the active sheet of the workbook to begin.
• Secondly, go to Developer.
• Then click on Visual Basic.

• Next, select Insert and then click Module to get the module box.
• Later, select the Tools followed by References.

• Subsequently, check Microsoft XML, v3.0, and hit OK.

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

• Select cell C5.
• Input the formula below:

• Later, press the Enter key to view the result in cell C5.

• Furthermore, tap the Fill Handle icon.
• Critical, hold, and drag to cell C10.
• As a result, it will return the output as below.

• Like previously, pick cell D5.
• At this point, input the formula below:

• Later, press Tab to display the response in cell D5.

• Again, tap the Fill Handle icon.
• Hold and drag to cell D10.
• As a result, it will provide the intended outcome, as seen below.

Download Practice Workbook

The example workbook used during the workshop is available for free.

Conclusion

You can now use the steps we just talked about to Convert a Zip Code to Latitude and Longitude in Excel. Keep using those, and let us know if you have other ideas or ways to accomplish the task. Remember to leave any questions, comments, or ideas in the section below.

<< Go Back to Geocoding in Excel | Learn Excel