Degrees-Minutes-Second (DMS) to Decimal-Degrees (DD) with Excel

At some point you may be handed data in an Excel file that needs to be imported into a GIS but the coordinate columns are in degrees-minutes-seconds format. For optimum use in the GIS you require the coordinate data in decimal-degrees format. Let’s look at how to achieve this using Excel and the data below for three cities, Dublin, Paris, and Sydney.

Excel Original Data

Add a header for two new columns LONGITUDE and LATITUDE (or calls these whatever suits you, X, Y for example)

Excel Add Columns

Press ALT + F11 to bring up the Microsoft Visual Basic for Applications window. From the Insert dropdown menu, select Module. Copy and paste the code below into the Module window.

Function Convert_Decimal(Degree_Deg As String) As Double
 ' Declare the variables to be double precision floating-point.
 Dim degrees As Double
 Dim minutes As Double
 Dim seconds As Double
 ' Set degree to value before "°" of Argument Passed.
 degrees = Val(Left(Degree_Deg, InStr(1, Degree_Deg, "°") - 1))
 ' Set minutes to the value between the "°" and the "'"
 ' of the text string for the variable Degree_Deg divided by
 ' 60. The Val function converts the text string to a number.
 minutes = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "°") + 2, _
 InStr(1, Degree_Deg, "'") - InStr(1, Degree_Deg, _
 "°") - 2)) / 60
 ' Set seconds to the number to the right of "'" that is
 ' converted to a value and then divided by 3600.
 seconds = Val(Mid(Degree_Deg, InStr(1, Degree_Deg, "'") + _
 2, Len(Degree_Deg) - InStr(1, Degree_Deg, "'") - 2)) _
 / 3600
 Convert_Decimal = degrees + minutes + seconds
End Function

Close the Microsoft Visual Basic for Applications window and return to the workbook.Click in the first cell under the LONGITUDE header (D2 above) and enter the formula


What this is saying is that if the last character of the cell is “W”, for West, then place a negative sign before the entry in the cell. If the last character is not a “W”, leave the entry as positive. See this post for an introduction to Geographic Coordinate Systems. Press enter to populate the cell with the decimal degree value.

Excel Long Function

Highlight the the new cell entry (D2) and grab the bottom right corner and drag down for the other two entries.

Excel Long Function All

Click into the cell under the LATITUDE header (E2) and enter the formula


Here, we have replaced the “W” with an “S” for South to account for negative coordinate values. C2 refers to the first cell under the LAT header. Click enter and grab the bottom right corner of the new cell entry and drag down for the other two records.

Excel Lat Function All

The code to convert to decimal degrees was sourced from the Microsoft Support website and can be found here, along with further information about the code and it’s application. The code, itself, does not compensate for negative coordinates so we have added a formula to use with the conversion to compensate for this.

Decimal-Degrees (DD) to Degrees-Minutes-Seconds (DMS) with Python

I am currently transitioning from the comfort of hiding behind buttons and converting to using programming for GIS and geospatial & geostatistical analysis. The code below is an implementation of converting decimal degrees to degrees-minutes-seconds format. The code is heavily commented and hopefully easy to understand.

import math

def dd2dms(longitude, latitude):

    # math.modf() splits whole number and decimal into tuple
    # eg 53.3478 becomes (0.3478, 53)
    split_degx = math.modf(longitude)
    # the whole number [index 1] is the degrees
    degrees_x = int(split_degx[1])

    # multiply the decimal part by 60: 0.3478 * 60 = 20.868
    # split the whole number part of the total as the minutes: 20
    # abs() absoulte value - no negative
    minutes_x = abs(int(math.modf(split_degx[0] * 60)[1]))

    # multiply the decimal part of the split above by 60 to get the seconds
    # 0.868 x 60 = 52.08, round excess decimal places to 2 places
    # abs() absoulte value - no negative
    seconds_x = abs(round(math.modf(split_degx[0] * 60)[0] * 60,2))

    # repeat for latitude
    split_degy = math.modf(latitude)
    degrees_y = int(split_degy[1])
    minutes_y = abs(int(math.modf(split_degy[0] * 60)[1]))
    seconds_y = abs(round(math.modf(split_degy[0] * 60)[0] * 60,2))

    # account for E/W & N/S
    if degrees_x < 0:
        EorW = "W"
        EorW = "E"

    if degrees_y < 0:
        NorS = "S"
        NorS = "N"

    # abs() remove negative from degrees, was only needed for if-else above
    print "\t" + str(abs(degrees_x)) + u"\u00b0 " + str(minutes_x) + "' " + str(seconds_x) + "\" " + EorW
    print "\t" + str(abs(degrees_y)) + u"\u00b0 " + str(minutes_y) + "' " + str(seconds_y) + "\" " + NorS

# some coords of cities
coords = [["Dublin", -6.2597, 53.3478],["Paris", 2.3508, 48.8567],["Sydney", 151.2094, -33.8650]]

# test dd2dms() 
for city,x,y in coords:
    print city + ":"
    dd2dms(x, y)

The output from running the above script is…

    6° 15' 34.92" W
    53° 20' 52.08" N
    2° 21' 2.88" E
    48° 51' 24.12" N
    151° 12' 33.84" E
    33° 51' 54.0" S

I am happy that the output of this script is the same as online converters.