The sample dataset has 2 types of columns (Location 1 and Location 2).
Method 1 – Using a Geometric Formula to Find the Distance Between Zip Codes
The geometric formula is commonly used to calculate the great circle distance between two points on a sphere based on their longitudes and latitudes.
- Select C5.
- In the Data tab, click Geography.
- C5 zip code will be converted into a geography code.
- In C6, enter the following formula:
=C5.City- Press Enter to find the location name inside the.
- 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))- Press Enter to get the distance between two cities in kilometers.
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
The CONCATENATE function allows you to combine cells or text strings, whereas the SUBSTITUTE function allows you to replace specific text within a string with different text.
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!
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