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:

Excel convert degrees minutes seconds to decimal degrees

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:

Excel: How to Convert Time Duration to Minutes
Excel: How to Convert Percentage to Ratio
Excel: How to Convert Feet and Inches to Decimal
Excel: How to Convert UNIX Timestamp to Date

One Reply to “Excel: Convert Degrees Minutes Seconds to Decimal Degrees”

  1. Hi,
    thanks, just what I searched for. I haven’t been able to get it to work yet, but will try to find out how to..
    One other thing, also in same field. If the position is west and south, I would like to have this decimal grade as a negative number. Can this be achieved in same cell? Or do I need to operate this after converting to decimal grade?
    Many thanks

Leave a Reply

Your email address will not be published. Required fields are marked *