How to Convert Degrees, Minutes and Seconds to Decimal Degrees in Excel – 2 Methods

This is the sample dataset.

Method 1 – Applying  a Formula to Convert Degrees, Minutes and Seconds to Decimal Degrees in Excel

Step 1:

• Copy the degree (°)

Step 2:

• Select the cells.

Step 3:

• Click Data.
• Select Text to Columns.

Step 4:

• Choose Delimited.
• Click Next.

Step 5:

• In Delimiters, click Other.

Step 6:

• Paste the degree (°).
• Click Next.

Step 7:

• Click Finish.

Step 8:

Degrees are separated and the symbols are removed.

Step 9:

• To remove the minutes (’) symbol, repeat the previous steps.
• Paste the minutes (’) symbol in the box.
• Click Next.

Step 10:

• Click Finish.

Step 11:

• To eliminate the seconds (”) symbol, paste the symbol in the box.
• Select Next.

Step 12:

• Click Finish.

Degrees will be separated into degrees, minutes, and seconds without the symbols.

Step 13:

• Enter the formula in  F5  to convert the degrees, minutes, and seconds into decimal degrees.

Step 14:

• Press Enter.

Step 15:

• Drag down the Fill Handle to see the result in the rest of the cells..

Method 2 – Running a VBA Code to Convert Degrees, Minutes and Seconds to Decimal Degrees in Excel

Step 1:

• Press  Alt  + F11  to open a Macro.
• Click Insert.
• Choose Module.

Step 2:

• Enter the following VBA.

Function Convert_to_Decimal(Degree As String) As Double
    'At first Declare the variables
   Dim deg As Double
   Dim min As Double
   Dim sec As Double
   ' Replace Degree set degree to value before "°"
   Degree = Replace(Degree, "~", "°")
   deg = CDbl(Left(Degree, InStr(1, Degree, "°") - 1))
   ' between "°" and "'" set minutes to a value
   ' as for the Degree variable divided by
   ' 60. Val is a function that turns a text string into a number.
   min = CDbl(Mid(Degree, InStr(1, Degree, "°") + 1, _
             InStr(1, Degree, "'") - InStr(1, Degree, "°") - 1)) / 60
   ' Set the second counter to the number to the right of "'," which is
   ' changed to a number, and after that divided by 3600
   sec = CDbl(Mid(Degree, InStr(1, Degree, "'") + _
           1, Len(Degree) - InStr(1, Degree, "'") - 1)) / 3600
   Convert_to_Decimal = deg + min + sec
End Function

• Save and press  F5  to run it.

Step 3:

• The function Convert_to_Decimal will be displayed. Enter the following formula.

• This is the output.

Step 4:

• Use the AutoFill tool to fill the other cells.

Download Practice Workbook

Download this practice workbook to exercise.

<< Go Back to Geocoding in Excel | Learn Excel