Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

# How to Calculate Distance Between Two Addresses in Excel (3 Ways)

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.

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

θ = 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. `=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.

### 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," ","+"))` • Next, press the ENTER button and you will see the Google Map link in C8.  Finally, you can calculate the distance between two addresses of the earth in Excel by applying CONCATENATE and SUBSTITUTE functions.

### 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.

## 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.

## Related Articles #### Nahian

Hello, Nahian here! I do enjoy my efforts to help you understand some little basics on Microsoft Excel I've completed my graduation in Electrical & Electronic Engineering from BUET and I want to be a successful engineer in my life through intellect and hard-work, and that is the goal of my career.

1. Reply • Reply  