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.

- 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:

`=C5.City`

- 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:

`=C6.Longitude`

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

- Then, enter the formula below in cell
**C8**:

`=C6.Latitude`

- 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:

`=6371*2*ASIN(SQRT(SIN((RADIANS(F8)-RADIANS(C8))/2)^2+COS(RADIANS(C8))*COS(RADIANS(F8))*SIN((RADIANS(F7)-RADIANS(C7))/2)^2))`

- 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:

`=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",SUBSTITUTE(C6," ","+"),"&daddr=",SUBSTITUTE(F6," ","+"))`

- 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**

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