Table of Contents
Converting degrees, minutes, and seconds to decimal degrees in Excel is a simple process that can be achieved by following these steps:
1. First, enter the degrees, minutes, and seconds values into separate cells in Excel.
2. Next, create a new cell where you want the decimal degrees result to appear.
3. In this new cell, use the following formula: =degrees+(minutes/60)+(seconds/3600)
4. Replace “degrees,” “minutes,” and “seconds” with the corresponding cell references where you entered the values in step 1.
5. Press Enter to calculate the decimal degrees value.
6. The result will be displayed in the new cell in decimal degrees format.
This method allows for an accurate conversion from degrees, minutes, and seconds to decimal degrees in Excel, making it a useful tool for various mathematical and geographical calculations.
Excel: Convert Degrees Minutes Seconds to Decimal Degrees
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
Additional Resources
The following tutorials explain how to perform other common operations in Excel: