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.

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

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

=IF(RIGHT(B2,1)="W",-Convert_Decimal(B2),Convert_Decimal(B2))

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.

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

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

=IF(RIGHT(C2,1)="S",-Convert_Decimal(C2),Convert_Decimal(C2))

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.

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.