Table of Contents
Excel is a powerful tool for many tasks, including the ability to convert degrees minutes seconds to decimal degrees. This is a useful skill for anyone who works with geographic coordinates, as decimal degrees provide a simpler representation of a location than degrees minutes seconds. In this article, we will explore the process of performing this conversion in Excel. We will also discuss why it is important to understand this conversion and how it can be used in various applications.
You can use the following formula to convert degrees minutes and seconds to decimal degrees in Excel:
=TEXTBEFORE(A2, "°")+TEXTBEFORE(TEXTAFTER(A2, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/3600
This particular formula will convert the degrees minutes and seconds in cell A2 to decimal degrees.
For example, if cell A2 contains 48° 51′ 52.9776″ N then this formula will return 48.86472.
The following example shows how to use this formula in practice.
Example: Convert Degrees Minutes Seconds to Decimal Degrees in Excel
Suppose we have the following latitude and longitude in Excel that are currently formatted as degrees minutes and seconds:
We can type the following formula into cell D2 to convert the latitude to decimal degrees:
=TEXTBEFORE(A2, "°")+TEXTBEFORE(TEXTAFTER(A2, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/3600
The following screenshot shows how to use this formula in practice:
The formula correctly returns 48.864716.
Next, we can type the following formula into cell E2 to convert the longitude to decimal degrees:
=TEXTBEFORE(B2, "°")+TEXTBEFORE(TEXTAFTER(B2, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(B2,"'"),"""")/3600
The following screenshot shows how to use this formula in practice:
The formula correctly returns 2.349014.
We have now successfully converted the longitude and latitude values from degrees minutes and seconds to decimal degrees.
How This Formula Works
Recall that cell A2 contained 48° 51′ 52.9776″ N.
Here is the formula that we used to convert the latitude degrees minutes and seconds in cell A2 to decimal degrees:
=TEXTBEFORE(A2, "°")+TEXTBEFORE(TEXTAFTER(A2, "°"), "'")/60+TEXTBEFORE(TEXTAFTER(A2,"'"),"""")/3600
Here is how this formula works:
First, we use TEXTBEFORE(A2, “°”) to extract the text before the first degree symbol in cell A2.
This extracts 48.
Next, we use TEXTBEFORE(TEXTAFTER(A2,”°”),”‘”)/60 to extract the text between the degree symbol and the single quote and divide it by 60.
This results in 51/60 = 0.85.
Lastly, we use TEXTBEFORE(TEXTAFTER(A2,”‘”),””””)/3600 to extract the text between the single quote and the double quote and divide it by 3600.
This results in 52.9776/3600 = .014716.
This produces the final result of 48 + .85 + .014716 = 48.864716