Farihat
Organizing data using various techniques is quite common in Microsoft Excel. Records are arranged in a tabular form here. In the case of data for latitude longitudes, angular values, degrees, minutes, and seconds are used as units. Although it is convenient to understand, this unit might be difficult to use for further calculations from these. This article will help you with three ways to convert degrees, minutes, and seconds to decimal degrees in Excel. It is applicable for multiple cells in Excel and you can eventually modify it according to your requirements. All the methods have step-by-step instructions with a figurative description.
📁 Download Excel File
Download the Excel file below.
Learn to Convert Degrees Minutes Seconds to Decimal Degrees in Excel with These 3 Suitable Approaches
The article is going to introduce three approaches in Excel to convert degrees, minutes, and seconds to decimal degrees. The methods will help you understand the uses elaborately. The following discussion will cover the uses of the LEFT, FIND, and MID functions. You can also use the Text to Columns option and VBA Macro Code feature to convert degrees, minutes, and seconds to decimal degrees in Excel. Therefore, users will get a walkthrough of these solutions. A dataset will be modified in the following methods below to help you understand.
1. Combining LEFT, FIND and MID Functions
To convert degrees, minutes, and seconds to decimal degrees in Excel, you can use The LEFT function, the FIND function, and the MID function.
⬇️⬇️ STEPS ⬇️⬇️
- Firstly, select cell D6.
- Secondly, enter the following formula.
- Thirdly, hit the Enter key.
- Fourth, copy the formulas down their cells using the Fill Handle icon.
🔨 Formula Breakdown
LEFT(C6,FIND(“°”,C6,1)-1)+(MID(C6,FIND(“°”,C6,1)+1,(FIND(“ʹ”,C6,1)-FIND(“°”,C6,1))-1)/60)+(MID(C6,FIND(“ʹ”,C6,1)+1,(FIND(“ʺ”,C6,1)-FIND(“ʹ”,C6,1))-1)/3600)
👉 Here, FIND looks for “°”, “ʹ”, “ʺ” within C6 starting from the first character.
👉 Also, MID takes C6 as text string. It returns character outputs as the returns from the FIND function.
👉 Finally, LEFT considers C6 as a text string. It calculates data based on the returns of former functions.
Therefore, you get the direct decimal degree values. 42.63 based on C6
Read More: Convert Degrees Decimal Minutes to Decimal Degrees in Excel
2. Utilizing Text to Columns Feature
Although Text to Columns is a complex option, it can help you to calculate decimal degrees in precision if you convert degrees, minutes, and seconds in Excel.
⬇️⬇️ STEPS ⬇️⬇️
- Initially, copy the “°” symbol from the data source using Ctrl+C.
- Now, select the data range C6:C9.
- Next, choose the Text to Columns option from the Data Tools group under the Data tab.
- Here, you will get the “Step 1” dialogue box of the Convert Text to Columns Wizard option.
- Then, select “Delimited” and proceed to “Next”.
- After that, the “Step 2” dialogue box will pop up.
- Now, choose “Other” from the Delimiters list.
- Here, paste the copied symbol by Ctrl+V.
- Then, proceed to the next step by clicking the Next button.
- Next, select the Finish button from the “Step 3” dialogue box.
- Here, you will get a warning since there are already data. Hit the OK button.
- Then, you will get the degree values separately as the image shows.
- Next, select the adjacent columns and repeat the same process for individual symbols following “‘” and ““”.
- After that, you will receive degrees, minutes, and seconds individually.
- Now, select cell F6.
- Here, enter the following formula.
- Hit the Enter key.
- Finally, copy the formulas down their cells using the Fill Handle icon.
Read More: 3 Ways to Fix If Excel is Not Recognizing Numbers in Cells
3. Applying User-Defined Function
Using VBA (Visual Basic for Applications), you can employ Macro code to apply on your worksheet. Moreover, it is also the only method that will create a new function to convert your degrees, minutes, and seconds values to decimal degrees values in Excel automatically. With the proper instruction, you can make your work easier.
⬇️⬇️ STEPS ⬇️⬇️
- In the beginning, press Alt+F11 to open Visual Basic Application (VBA).
- Next, select the Module option from the Insert tab. You can also press Alt+I+L for the action.
- Then, write the following code on the Module box.
Function Dec_deg(Deg_M_S As String) As Double
Dim degrees As Double
Dim minutes As Double
Dim seconds As Double
Deg_M_S = Replace(Deg_M_S, "~", "°")
degrees = CDbl(Left(Deg_M_S, InStr(1, Deg_M_S, "°") - 1))
minutes = CDbl(Mid(Deg_M_S, InStr(1, Deg_M_S, "°") + 1, _
InStr(1, Deg_M_S, "'") - InStr(1, Deg_M_S, "°") - 1)) / 60
seconds = CDbl(Mid(Deg_M_S, InStr(1, Deg_M_S, "'") + _
1, Len(Deg_M_S) - InStr(1, Deg_M_S, "'") - 1)) / 3600
Dec_deg = degrees + minutes + seconds
End Function- Here, you should see the window like the image below.
- Now, select cell D6.
- After that, enter the following formula.
- Next, hit the Enter key.
- Finally, copy the formulas down their cells using the Fill Handle icon.
📄 Important Notes
🖊️ You should modify your formulas according to your dataset.
🖊️ You need to be careful while using symbols for degrees, minutes, and seconds to convert to decimal characters in Excel. The formula and dataset must maintain the same symbols.
🖊️ Finally, you need to enable Macro Workbook for the VBA to run on your workbook.
📝 Takeaways from This Article
The article lets the readers understand the variety of options available to convert degrees, minutes, and seconds to decimal degrees in Excel.
📌 Primarily, you can use a formula with the help of LEFT, FIND, and MID functions. This formula can directly convert your degrees, minutes, and seconds to decimal degrees.
📌 Also, you can use the Text to Columns option in an elaborate way to calculate the decimal degrees manually.
📌 You can employ VBA Macro Code to convert degrees, minutes, and seconds to decimal degrees in Excel.
Conclusion
To count duplicates in two columns in Excel, three methods are useful. You can use the LEFT, FIND, and MID functions. The Text to Columns option and VBA Macro Code feature can help you to convert degrees, minutes, and seconds to decimal degrees in Excel as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.
