Changing DMS into decimal degrees

[kplunkett]

Hello! I have a column of GPS coordinates in DMS style (like this: 31°59'42.5"N 87°17'37.0"W) but for some reason they won't upload to ArcGIS, so I was told to try changing them into decimal degrees (like this:
30.9951389, -87.29361). Is there a way to have Excel do the change automatically? I have over 3k entries and am dreading doing it by hand. Thank you all!!

 

[Micron]

I believe the formula is Degree+Minute/60+Second/3600 so 31°59'42.5"N becomes 31.99513889
Thus you could simply use text to columns and create a helper column that uses the formula on the split data. Probably faster than coding anything.

 

[kevin9999]

Please try the following on a copy of your data. Put the following code in a standard module:

Function DMS2DEC(s As String) As String
    Dim Sth As Boolean, Wst As Boolean, tmp
    Dim Lat As Double, Lon As Double
    If s Like "*S*" Then Sth = True
    If s Like "*W*" Then Wst = True
    Change = Array("°", """", "'")
    For Each a In Change
        s = Replace(s, a, " ")
    Next a
    tmp = Split(s, " ")
    
    If Sth = False Then
        Lat = CDbl(tmp(0)) + CDbl(tmp(1)) / 60 + CDbl(tmp(2)) / 3600
    Else
        Lat = -CDbl(tmp(0)) - CDbl(tmp(1)) / 60 - CDbl(tmp(2)) / 3600
    End If
    
    If Wst = False Then
        Lon = CDbl(tmp(4)) + CDbl(tmp(5)) / 60 + CDbl(tmp(6)) / 3600
    Else
        Lon = -CDbl(tmp(4)) - CDbl(tmp(5)) / 60 - CDbl(tmp(6)) / 3600
    End If
    DMS2DEC = Format(Lat, "#.00000000") & ", " & Format(Lon, "#.00000000")
End Function

Use it on your sheet like this (copy down to as many rows a you need).

Book1
	A	B
1	31°59'42.5"N 87°17'37.0"W	31.99513889, -87.29361111
2	31°59'42.5"S 87°17'37.0"W	-31.99513889, -87.29361111
3	31°59'42.5"N 87°17'37.0"E	31.99513889, 87.29361111
4	31°59'42.5"S 87°17'37.0"E	-31.99513889, 87.29361111
Sheet2

Cell Formulas
Range	Formula
B1:B4	B1	=DMS2DEC(A1)