The article will show you how to calculate the **distance **between two addresses in Excel. Microsoft Excel has this cool special feature and formula for **finding the distance** between two places in the world. If you have **GPS Coordinates **of two places, you can easily **determine the distance between those places** in any unit you want.

**Table of Contents**hide

## Download Practice Workbook

## 3 Ways to Calculate Distance Between Two Addresses in Excel

In our dataset, we have **Latitude **and **Longitude **for **Ohio **and **Alaska**. We are going to measure the distance between them.

The **Latitudes **for **Ohio **and **Alaska **are **40.4173 North **and **64.2008 North **respectively. Also, the **Longitudes **for **Ohio **and **Alaska **are **82.9071 West **and **149.4937 West **respectively.

**1. Using Haversine Formula to Calculate Distance Between Two Addresses **

If you are good at mathematics, using **Haversine Formula **will be perfect for you. Although it will give you an approximate result, it’s a pretty good one.

First of all, we need to know the **basic formula of arc length**. And using that formula, we compare it with the distance between two places on earth. The formula is given below.

**S = rθ**

**S = Distance between two addresses**

**r = Radius of Earth**

** θ = Angle introduced at the center of the Earth by the two addresses**

But if you have **GPS Coordinates **of two places, you need to determine from the **Haversine Formula**. Look at the **Haversine Formula **shown below.

If you compare this with the **arc length formula**, you will get like in the following image.

Let’s introduce you to the parameters of the **Haversine Formula**.

**φ _{1} **

**= Latitude of the first place**

**φ _{2} = Latitude of the second place**

**ℽ**_{1}** = Longitude of the first place**

**ℽ**_{2}** = Latitude of the second place**

Now, I’ll be showing you how to apply this formula in Excel step by step.

**Steps:**

- First, make a cell to store the distance value and type the following formula in cell
**C8**.

`=2*6400*ASIN(SQRT((SIN(C6-C5)/2)^2+COS(C5)*COS(C6)*(SIN(D6-D5)/2)^2))`

The formula uses **ASIN**, **SQRT**, **SIN** and **COS**** functions**. It’s pretty simple if you just look at the **Haversine Formula**. We measure the distance in **kilometers**, so we put the radius of the earth in kilometers which is **6400 km**. **ASIN **refers to the **inverse Sine **or the **ArcSine**. If we compare the parameter angles of the **Haversine Formula **with our Excel formula, we get,

_{1 }**= Latitude of Ohio (C5)**

_{2}** = Latitude of Alaska (C6)**

**ℽ**_{1}** = Longitude of Ohio (D5)**

**ℽ**_{2}** = Latitude of Alaska (D6)**

- After that, press the
**ENTER**button to see the distance between**Ohio**and**Alaska**in**Kilometers**.

- Thereafter, if you want to
**measure the distance in miles**, use the following formula in cell**C8**.

`=2*3959*ASIN(SQRT((SIN(C6-C5)/2)^2+COS(C5)*COS(C6)*(SIN(D6-D5)/2)^2))`

Here, we used the same Excel formula we used to find the distance in **miles**. For that reason, we take the earth’s radius in **miles **which is **3959**.

Thus you can calculate the **distance** between two addresses of earth in Excel by applying the **Haversine Formula**.

**Read More: ****How to Calculate Distance Between Two Coordinates in Excel (2 Methods)**

**2. Applying Excel CONCATENATE and SUBSTITUTE Functions to Calculate Distance Between Two Addresses**

You can create a **Google Map **link to find the **distance **between two addresses using Excel **CONCATENATE** and **SUBSTITUTE**** Functions**. Let’s go through the process below.

**Steps:**

- First, type the following formula in
**C8**.

`=CONCATENATE("http://maps.google.co.uk/maps?f=d&source=s_d&saddr=",`

`SUBSTITUTE(B5," ","+"),"&daddr=",SUBSTITUTE(B6," ","+"))`

This formula will create a **Google Map **link of how you can travel from **Ohio **to **Alaska**. The** CONCATENATE **function will add the **addresses **in the link and the** SUBSTITUTE **function will place the name of the **addresses**.

- Next, press the
**ENTER**button and you will see the**Google Map**link in**C8**.

- After that, use this
**link**in your internet search bar and you will get the**distance**information about these**two addresses**.

Finally, you can calculate the **distance** between two addresses of the earth in Excel by applying **CONCATENATE **and **SUBSTITUTE **functions.

**Read More:** **How to Calculate Distance Between Two Cities in Excel**

**3. Using VBA to Calculate Distance Between Two Addresses in Excel**

Another way to calculate **distance** between two addresses could be by making an **API** (**Application Programming Interface**) link and using it to create a **User Defined function** by **VBA**. You can make an **API **link in **Bing **for free. To create a **Bing Map** API Key of your own, **click here**.

**Steps:**

- First, go to
**Developer**>>**Visual Basic**.

- After that, select
**Insert**>>**Module**to open the**VBA Module**.

- Later, type the following code in the
**VBA Module**. We are creating an**User Defined Function**to calculate**distance**in**miles**.

```
Option Explicit
Public Function DistanceInMiles(First_Location As String, _
Final_Location As String, Target_Value As String)
Dim Initial_Point As String, Ending_Point As String, _
Distance_Unit As String, Setup_HTTP As Object, Output_Url As String
Initial_Point = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Ending_Point = "&destinations="
Distance_Unit = "&travelMode=driving&o=xml&key=" & Target_Value & "&distanceUnit=mi"
Set Setup_HTTP = CreateObject("MSXML2.ServerXMLHTTP")
Output_Url = Initial_Point & First_Location & Ending_Point & Final_Location & Distance_Unit
Setup_HTTP.Open "GET", Output_Url, False
Setup_HTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
Setup_HTTP.Send ("")
DistanceInMiles = Round(Round(WorksheetFunction.FilterXML(Setup_HTTP.ResponseText, _
"//TravelDistance"), 3), 0)
End Function
```

**Code Explanation**

- First, we named our
**Function**as**DistanceInMiles**. We also inserted**3**parameters:**First_Location As String, Final_Location As String**and**Target_Value As String**. - After that, we declared
**Initial_Point**,**Ending_Point**,**Distance_Unit**and**Outout_Url**as**String**;**Setup_HTTP**as**Object**. - Later, we set
**Initial_Point**as the starting of the**Url link**,**Ending_Point**as**Destination**and**Distance_Unit**to**Miles**. - After that, we setthe necessary parameters to create a relation between our
**VBA**code and the**API** - Finally, we established our
**User Defined Function**.

- After that, following the same procedures, we made another
**User Defined Function**to find the distance in**kilometers**.

You can see that, we just changed the **Distance_Unit **to **kilometers**.

- Thereafter, in the following picture, you can see the
**API**key in cell**C8**.

- After that, type the following formula in cell
**C8**.

`=DistanceInMiles(E5,E6,C8)`

- Next, press the
**ENTER**button and you will see the**distance**between**Ohio**and**Alaska**in**miles**.

- After that, use the following formula to see the
**distance**in**kilometer**.

`=DistanceInKM(E5,E6,C8)`

Thus, you can calculate the **distance** between two addresses using **VBA **and **API **key.

**Read More: ****How to Calculate Driving Distance between Two Addresses in Excel**

**Things to Remember**

- Keep that in mind, you need to put approximately correct
**GPS Coordinates**of these**states**in the data because you are using the internet to find the distance. As the**states**are**west**to the**Meridian Line**, both of their**Longitudes**will be negative.

## Practice Section

In this section, I’ll give you the dataset of this article so that you can practice these methods on your own.

## Conclusion

Suffice to say, you will learn very effective methods of how to calculate **distance **between two addresses in Excel after reading this article. If you have any better methods or questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles. For more queries, kindly visit our website **ExcelDemy.com**.

Isn’t Haversine Formula required an angle in rad unit?

Hello L.C., thank you for reaching out. The angles in the formula are used as input for the Sine and Cosine functions. So the conversion from degree to radian isn’t necessary. Whether I change the angle unit to radian or not, the value of the corresponding Sine or Cosine function will be the same as the angles remain the same.