# 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.
`=B5+C5/60+D5/3600`

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.
`=Convert_to_Decimal(B5)`

• This is the output.

Step 4:

• Use the AutoFill tool to fill the other cells.

<< Go Back to Geocoding in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

1. Hi I am trying to use Convert_to_Decimal in VB. I have also tried the function from the Microsoft website, which appears to be the same coding. I am unable to get it to work, it returns a #VALUE. I don’t have a great understanding of the code and am wondering if other users have come across this issue and there is a solution.
Could you help me with this issue.

Much appreciation
Justin

• Greetings Justin,
#VALUE error occurs when the function doesn’t find any recognized source value. It might have occurred if the degree values were not in degree formats. Follow the steps below to convert the value into degree format first and then apply the function Convert_to_Decimal with our VBA.

Step 1:
Add a degree (°) symbol after a number with a keyboard shortcut.

``Alt + 0176``

(Example: 32°).

Step 2:
Add a minute (‘) symbol after the minute’s value with the apostrophe (‘) key from the keyboard (Example: 16’).

Step 3:
Add a second (“) symbol after the second’s value by pressing

``SHIFT + Apostrophe (')``

(Example: 48″)

Step 4:
Your degree format will show like (32°16’48”).
Finally, apply the Convert_to_Decimal function to the degree value.

Hope this will work with your issue. You can also mail us the Excel file. We will provide the Excel file with the required solution. Please give us your further feedback.

Advanced Excel Exercises with Solutions PDF