How to Find the Distance Between Zip Codes Using a Formula in Excel – 2 Methods

The sample dataset has 2 types of columns (Location 1 and Location 2).

distance between zip codes excel formula


Method 1 – Using a Geometric Formula to Find the Distance Between Zip Codes

Steps:

  • Select C5.
  • In the Data tab, click Geography.
  • C5 zip code will be converted into a geography code.

converting to geography to find the distance between zip codes in Excel using a formula

  • In C6, enter the following formula:
=C5.City
  • Press Enter to find the location name inside the.

finding location name to find the distance between zip codes in Excel using a formula

  • In C7, enter this formula:
=C6.Longitude
  • Press Enter to find the longitude of the location.

  • Enter the formula below in C8:
=C6.Latitude
  • Press Enter to get the latitude value.
  • Follow the two previous steps for location 2.

  • In C10, enter the formula below:
=6371*2*ASIN(SQRT(SIN((RADIANS(F8)-RADIANS(C8))/2)^2+COS(RADIANS(C8))*COS(RADIANS(F8))*SIN((RADIANS(F7)-RADIANS(C7))/2)^2))

distance between zip codes excel formula

The formula takes the longitude and latitude coordinates of the two points and returns the distance between them in kilometers. It first converts the longitude and latitude coordinates from degrees to radians using the RADIANS function. It calculates the distance using the ASIN, SQRT, SIN, and COS functions. The final result is multiplied by the values 6371 and 2, which is the approximate radius of the Earth in kilometers.


Method 2 – Combining the CONCATENATE and SUBSTITUTE Functions

Steps:

  • Generate the location names following the steps in Method 1.
  • In C8, enter the formula below:
=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",SUBSTITUTE(C6," ","+"),"&daddr=",SUBSTITUTE(F6," ","+"))
  • Press Enter. A google maps link will be displayed in C8.
  • Select C8 and press Ctrl+C to copy the link.

The CONCATENATE function adds the addresses to the Google Maps link and the SUBSTITUTE function inserts the names of the addresses.

  • Paste this link into the browser to see the distance between the two zip code locations.


Things to Remember

  • When using the geometric formula, remember to convert the longitude and latitude coordinates from degrees to radians before entering them into the formula.
  • Pay attention to the order in which you combine cells and text strings when using the CONCATENATE and SUBSTITUTE functions, and make sure to specify the text you want to replace.

Download Practice Workbook

Download the practice workbook here.


<< Go Back to Distance | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Nazmul Hossain Shovon
Nazmul Hossain Shovon

Nazmul Hossain Shovon, a BUET graduate in Naval Architecture and Marine Engineering, embarked on his career with 8 months dedicated to the Exceldemy project's triumph. Transitioning into a Software Developer role, he specialized in web add-in development. At Exceldemy, he authored about 125 blog articles and solved many visitors’ problems, refining his writing skills and delving into Excel-related topics. With a primary passion for programming and software development, Shovon continually explores new horizons, fostering professional growth in his... Read Full Bio

2 Comments
  1. Hi! I am using method #1. I checked the math using google maps on Bell Gardens, CA 90201 to Seattle, Washington and the distance (as crow flies) is closer to 1,572 km or 976 miles. This is about twice what your calcuations show. Also, when I do this calcuation on different zip codes I am usually half of the real distance (as crow flies). What am I missing? Should we be multiplying by 2 for some reason to get the distance?

    • Dear KRIS,
      Thanks for your feedback. We are extremely sorry that, we missed multiplying the formula by 2 (method-1). Now we have corrected the formula in the article, please try it now.
      If you still have any queries, please let us know in the comment section or our forum.

      Thanks and regards,
      Md. Mizbahul Abedin
      Excel & VBA Content Developer

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo