ExcelInsider Team
While latitudes and longitudes are usually expressed in degrees, minutes, and seconds, not all programs can use this format. In GIS software, you need to input the data in decimal degrees, or it won’t accept your input. In this article, we will learn four ways to convert degrees, minutes, and seconds to decimal degrees in excel.
➤ Take a cell where you want the decimal degrees to show up, preferably in the same row.
➤ Insert this formula:
=TEXTBEFORE(B2, “°”)+TEXTBEFORE(TEXTAFTER(B2, “°”), “‘”)/60+TEXTBEFORE(TEXTAFTER(B2,”‘”),””””)/3600
➤ Replace B2 with your input cell and press Enter.
➤ Autofill the other rows in the column.
That was one way to do the job. But in this article, we will have three more ways with explanations so that you can understand clearly how to do this job. So, stick around and try to learn something by reading this attentively.
Using TEXTBEFORE-TEXTAFTER Functions
In this example dataset, we have the latitudes and longitudes of some reserves. The values are in DMS format, and we need to convert them to decimal degrees in order to work with them later. Follow the methods below to do that:
The latitudes and longitudes are put in excel using a normal string format. Using the TEXTBEFORE and TEXTAFTER functions, we can parse the data and calculate the decimal degrees.
➤ Take a helper column to fill it with decimal data.
➤ Insert this formula in the first cell:
=TEXTBEFORE(B2, “°”)+TEXTBEFORE(TEXTAFTER(B2, “°”), “‘”)/60+TEXTBEFORE(TEXTAFTER(B2,”‘”),””””)/3600
➤ Fill the whole column by autofill.
Combining LEFT-FIND-MID Functions
This formula uses three functions to parse the text with degrees, minutes, and seconds. The formula is long, but it gets the job done.
➤ In the helper column, write this formula:
=LEFT(B2, FIND(“°”, B2) – 1) + (MID(B2, FIND(“°”, B2) + 1, FIND(“‘”, B2) – FIND(“°”, B2) – 1) / 60) + (MID(B2, FIND(“‘”, B2) + 1, FIND(“”””, B2) – FIND(“‘”, B2) – 1) / 3600)
➤ Don’t forget to autofill other cells.
Inserting Formula with SUM-TEXTSPLIT Functions
This formula is rather shorter than the last one. We are going to use a simple formula that parses the input cell without handpicking all values one by one. The process, including explanations, is below:
➤ Put this formula in the output cell:
=SUM(DROP(TEXTSPLIT(B2,{“°”,”‘”,””””}), , -1)/{1,60,3600})
➤ Fill the other cells using your mouse.
Using VBA Code
Get ready to use some programming to do the job. We will be using the built-in VBA programming in Microsoft Excel to create our own function and then use that function for the spreadsheet. Follow the steps below:
➤ When you have the spreadsheet open, press Alt+F11 to open the programming interface.
➤ Go to Insert > Module
➤ Write this code in the code editor:
Function ConvertToDecimal(DMSString As String) As Double
Dim deg As Double
Dim min As Double
Dim sec As Double
Dim degreePos As Long
Dim minutePos As Long
Dim secondPos As Long
DMSString = Replace(DMSString, "~", "°")
degreePos = InStr(1, DMSString, "°")
minutePos = InStr(1, DMSString, "'")
secondPos = InStr(1, DMSString, """")
If degreePos = 0 Or minutePos = 0 Or secondPos = 0 Then
ConvertToDecimal = CVErr(xlErrValue)
Exit Function
End If
deg = CDbl(Left(DMSString, degreePos - 1))
min = CDbl(Mid(DMSString, degreePos + 1, minutePos - degreePos - 1)) / 60
sec = CDbl(Mid(DMSString, minutePos + 1, secondPos - minutePos - 1)) / 3600
ConvertToDecimal = deg + min + sec
End Function
➤ Go back to your spreadsheet. Now write this formula:
=ConvertToDecimal(B2)
➤ Press Enter and autofill the rest of the column.
Frequently Asked Questions
What is the formula for DMS?
The formula is DMS = Degrees + (Minutes / 60) + (Seconds / 3600)
How to format degrees in Excel?
Enable the Numpad on your keyboard. Go to a cell, and press Alt+0176 to enter the degree symbol.
How to convert minutes and seconds to decimal in Google Sheets?
Use this formula:
=LEFT(A1, SEARCH(“°”, A1)-1) + MID(A1, SEARCH(“°”, A1)+1, SEARCH(“‘”, A1) – SEARCH(“°”, A1) – 1)/60 + MID(A1, SEARCH(“‘”, A1)+1, SEARCH(“\””, A1) – SEARCH(“‘”, A1) – 1)/3600
Replace A1 with your input cell.
How do you convert degrees minutes seconds to radians in Excel?
First, convert degrees, minutes, and seconds to decimal degrees. Then use this function:
=RADIANS(A1)
Replace A1 with your cell name.
Does Excel recognize degrees?
Yes. Using the DEGREES function, you can even convert radians to degrees. Here’s an example with A1 as the input cell.
=DEGREES(A1)
Wrapping Up
In this article, you have learned four ways to convert degrees, minutes, and seconds to decimal degrees in excel. We hope you can analyze your data accurately now. Use the practice file to learn properly, and leave a comment with your opinions below.
