To denote angular dimensions, degrees, minutes, and seconds are commonly used (DMS). Because one minute equals 60 seconds and one degree equals 60 minutes, you may describe angular observations in degrees and decimal fractions of degrees to simplify mathematical calculations. This article explains some customized functions to convert degrees, minutes, and seconds to decimal degrees in Excel.
Convert Degrees Minutes Seconds to Decimal Degrees in Excel: 2 Ways
We’ll utilize Excel’s built-in options first, then use a formula to convert the degrees to decimal format in the sections below. Later on, we’ll use VBA code to create a custom option for decimal conversion. Here is an example data set that we will use to accomplish it.
1. Apply a Formula to Convert Degrees Minutes Seconds to Decimal Degrees in Excel
We’ll start by using a formula to convert degrees, minutes, and seconds to decimal format. To do so, we must first remove the degrees, minutes, and seconds symbols before using the formula. Follow the instructions below to do so.
- First of all, copy the degree (°)
- Select the cells.
- Click on the Data.
- Then, select the Text to Columns.
- Choose the Delimited.
- After that, click on the Next.
- From the options, click on the Other.
- Paste the degree (°).
- Then, click on the Next.
- After that, click on the Finish.
- Therefore, you see that degrees are separated and the symbols are removed.
- To remove the minutes (’) symbol, repeat the previous steps after copying the minutes (’).
- Simply, paste the minutes (’) symbol in the box.
- Then, click on the Next.
- As before, click on the Finish.
- Again, to eliminate the seconds (”) symbol, paste the symbol in the box.
- After that, select the Next.
- Finally, click on the Finish option again as we did before.
- As a result, the degrees will be separated into degrees, minutes, and seconds without the symbols.
- Type the formula in cell F5 to convert the degrees, minutes, and seconds into decimal degrees.
- Press Enter to see the conversion.
- Use the AutoFill tool to autofill the next cells.
2. Run a VBA Code to Convert Degrees Minutes Seconds to Decimal Degrees in Excel
When converting degrees to decimal fractions, VBA offers a handy option. We’ll create a custom function to do the conversion in a matter of seconds, regardless of the symbols involved. Follow the steps outlined below to complete it.
- Firstly, press Alt + F11 to start a Macro.
- Click on the Insert.
- Then choose the Module.
- Paste 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 the program and press F5 to run it.
- Therefore, you will see a new function will appear named Convert_to_Decimal. Simply, write the following formula.
- As a result, you will get the changed value of the degree.
- Finally, use the AutoFill tool to fill the next cells.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
To summarize, I hope you now have a firm grasp on how to convert degrees from minutes and seconds to decimal degrees in Excel. These methods should all be taught and used for your data. Examine the practice book and apply what you’ve learned. We’re inspired to keep producing seminars like this because of your important support.
If you have any questions, please do not hesitate to contact us. Please share your thoughts in the comments section below. Stay with us and continue to learn.
- How to Write Latitude and Longitude in Excel
- How to Create Latitude Longitude Converter in Excel
- How to Convert Lat Long to UTM in Excel
- Convert Decimal Coordinates to Degrees Minutes Seconds in Excel
- How to Convert Latitude and Longitude to Address in Excel
- How to Convert Address to Lat Long in Excel
- How to Convert ZIP Code to Latitude and Longitude in Excel
- How to Perform Reverse Geocoding in Excel