Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

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


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

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

convert zip code to latitude longitude excel


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.

Convert Zip Code to Latitude and Longitude Through Geography Data Type

  • Thirdly, select cell C5.
  • Now, put the formula below:
=B5.City.Latitude
  • 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:
=B5.City.Longitude
  • 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.

output of zip code in latitude longitude

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


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.

Apply Excel VBA to Convert the Zip Code to Latitude Longitude

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

output of Zip Code in Latitude Longitude by VBA

Read More: How to Create Latitude Longitude Converter in Excel (2 Examples)


Conclusion

You can now use the steps we just talked about to ConvertZip Code to Latitude and Longitude in Excel. You can find many articles like this on the ExcelDemy Website. 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.


Related Articles

Lutfor Rahman Shimanto

Lutfor Rahman Shimanto

Hi there! I am Lutfor Rahman Shimanto. I have completed my graduation in Information Technology from Jahangirnagar University. Currently, I am working as a technical content writer at ExcelDemy. You will find all my articles on Microsoft Excel on this site. Outside of work, I enjoy Chess a lot. I am a founding member of the Jahangirnagar University Chess Club and an internationally rated chess player.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo