How to Find Distance Between Zip Codes in Excel Using Formula

In this tutorial, I am going to show you 2 easy methods of how to find the distance between zip codes in Excel using a formula. You can quickly use these methods to find the distance between any two locations very easily. Throughout this tutorial, you will also learn some important Excel tools and functions that will be very useful in any Excel-related task.

How to Find Distance Between Zip Codes in Excel Using Formula: 2 Easy Methods

We have taken a concise dataset to explain the steps clearly. The dataset has approximately 6 rows and 2 types of columns. Initially, we are keeping all the cells in General format. For all the datasets, we have 2 unique columns which are Location 1 and Location 2. Although we may vary the number of columns later on if that is needed.

1. Using Geometric Formula in Excel to Find Distance Between Zip Codes

This first method will provide you with a step-by-step guide on how to use the geometric formula in Excel to calculate the distance between two zip codes. We will be using the geometric formula, which is a commonly used formula for calculating the great circle distance between two points on a sphere based on their longitudes and latitudes. By following the instructions in this method, you will be able to easily calculate the distance between two zip codes using Excel.

Steps:

• First, select cell C5 and click on Geography under the Data tab as in the image below.
• As a result, this will convert the cell C5 zip code to a geography code.

• Next, go to cell C6 and type in the following formula:

• Then, press the Enter key and this will find the location name inside cell C6.

• After that, navigate to cell C7 and insert this formula:

• Now, press Enter and this will find the longitude of the location.

• Then, enter the formula below in cell C8:

• Next, press Enter to get the latitude value.
• Similarly, follow the previous steps for location 2.

• Now, go to cell C10 and insert the formula below:

• Finally, press Enter to get the distance between two cities in kilometres.

The formula takes the longitude and latitude coordinates of the two points as input 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 then 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.

2. Combining CONCATENATE and SUBSTITUTE Functions

In this last method, we will show you how to use a powerful Excel formula to calculate the distance between two zip codes. The CONCATENATE function allows you to combine cells or text strings, while the SUBSTITUTE function allows you to replace specific text within a string with different text. By using these functions in combination, you can easily create a formula that will calculate the distance between two zip codes. By following the instructions below, you will be able to easily calculate the distance between two zip codes using Excel.

Steps:

• To begin with, generate the location names following the steps of Method 1.
• Next, go to cell C8 and insert the formula below:

• Then, press the Enter key and this will give you a google maps link in cell C8.
• Now, select cell C8 and press Ctrl+C to copy the google maps link.

The above formula will generate a Google Map link showing the route from Bay Minette to Argillite. It will do so by using the CONCATENATE function to add the addresses to the link and the SUBSTITUTE function to insert the names of the addresses.

• Finally, paste this link into the browser, which will immediately give you the distance between the two zip code locations.

By following the above steps, you should now have a solid understanding of how to use the CONCATENATE and SUBSTITUTE functions in Excel to calculate the distance between two zip codes.

Things to Remember

• Make sure you have an internet connection to your device to get the longitude and latitude coordinates for both zip codes in Excel.
• When using the geometric formula, remember to convert the longitude and latitude coordinates from degrees to radians before plugging 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 specific text you want to replace.
• Follow the instructions in the tutorial carefully to ensure that your formula is correct.
• Test your formula by inputting different zip codes and verifying that the output is correct.
• If you encounter any issues or have any questions, don’t hesitate to seek help from us.

Download Practice Workbook

You can download the practice workbook from here.

Conclusion

I hope that you were able to apply the methods that I showed in this tutorial on how to find the distance between zip codes in Excel using a formula. As you can see, there are quite a few ways to achieve this. So wisely choose the method that suits your situation best. If you get stuck in any of the steps, I recommend going through them a few times to clear up any confusion. If you have any queries, please let me know in the comments.

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