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

- 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

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

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