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.

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

The following tutorials explain how to perform other common operations in Excel: