Convert Latitude and Longitude to Decimal Degrees in Excel

Method 1 – Using a Manual Process

This is the sample dataset.

Step 1:

Separate Degree, Minute, and Second values in different columns.

A new column (DD) was added to store the converted result.

Enter the following formula in G5 and press ENTER.

=D5+E5/60+F5/3600

The value is displayed in Decimal Degrees.

Step 2:

Hold and drag down G5.

Decimal Degrees will be displayed in the other cells.

Step 3:

For Longitude values:

Separate Degree, Minute, and Second values in different columns.
Use the same formula in column G.

Method 2 – Using the String Manipulation Functions

Step 1:

Add two columns to display Decimal outputs.
Enter the following formula in E5 and press ENTER.

=LEFT(C5, FIND("°",C5,1) - 1)+(MID(C5,FIND("°",C5,1)+1,(FIND("’",C5,1)-FIND("°",C5,1))-1)/60)+(MID(C5,FIND("’",C5,1)+1,(FIND("”",C5,1)-FIND("’",C5,1))-1)/3600)

The LEFT function gets the values ahead of “°”. The MID function extracts the portion between “°” and “’”. The MID function portion extracts the value after “’”. Values are summed to return the final value. The FIND function helps to initiate the search within each portion.

Hold and drag down E5.

Decimal values are displayed.

Step 2:

For Longitude values:

Enter the following formula (used in Step 1) in F5 and Press ENTER.

=LEFT(D5, FIND("°",D5,1) - 1)+(MID(D5,FIND("°",D5,1)+1,(FIND("’",D5,1)-FIND("°",D5,1))-1)/60)+(MID(D5,FIND("’",D5,1)+1,(FIND("”",D5,1)-FIND("’",D5,1))-1)/3600)