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.