How to Convert ZIP Code to Latitude and Longitude in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

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


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

Note
Ensure to use a valid API endpoint. For example, while going through this, you may find that “https://nominatim.openstreetmap.org/search?format=xml&q=” is invalid. Sometimes, APIs change their URLs or endpoints. The API Key will become invalid if three months or 10K transactions are reached.
  • Select cell C5.
  • Input the formula below:
=@findLatitude(B5)
  • 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:
=@findLongitude(B5)
  • 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


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Lutfor Rahman Shimanto
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

2 Comments
  1. Can you please explain why the latitude/longitude values from Option 2 are all slightly different than the values from Option 1? Is Option 2 more accurate/precise than 1?

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto Oct 15, 2023 at 6:18 PM

      Hello HEATHER

      Thanks for reaching out and posting your comment. You are right about the results from using Geography Datatypes and Excel VBA user-defined functions being slightly different. However, a valid API endpoint in VBA code will give you accurate results like the Geography datatypes.

      If you are a Microsoft 365 user, I recommend using the first method, where you can use Geography datatype. I have presented the Excel VBA to other Excel users. However, I must admit that using the Geography is more accurate than the Excel VBA user-defined functions.

      Regards
      Lutfor Rahman Shimanto

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo