How to Convert DMS Coordinates to Decimal

[CLE]

Looking to convert DMS to decimal. Found the following in the Excel
Knowledgebase:

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

What's lacking is it doesn't allow converting south and west DMS coordinates
to negative Decimal coordinates.
Can anyone help with this?
Thank you!

 

[Jerry Porter]

CLE,

So if the input is 0-180°, you just want to convert to decimal, but if
it's 180° to 360°, you want a negative value? E.g. 270° = -90. Is
that right?

If so, try adding the following line just before End Function:

If Convert_Decimal > 180 Then Convert_Decimal = Convert_Decimal -
360

Jerry

 

[CLE]

Jerry,
Thank you, but that won't work. I probably assumed folks would know what
I'm talking about and didn't explain things very clearly. When converting
from DMS to Decimal, the only numbers that are "converted" are the minutes
and seconds. The 'number' for degrees remains the same. However, if the
orginal northing or easting was south or west, then the sign for the final
result in decimal format becomes negative. This is done when computing
distance between two points, and only in certain circumstances. However, I
need a function that will cover that.
The function described below only considers degrees/minutes/seconds and
not northing or easting. It also requires the input be formatted as
<<degreeso>> (with the 'o' on superscript) <<minutesâ€>> <<seconds’>> What
I'd like to do is be able to add the northing or easting to the input, and
modifiy the function to change the sign of the final product to negative if
the northing/easting is south or west.
Thanks!
CLE

 

[Jerry Porter]

It might be clearer if you give some examples of inputs and outputs. I
don't understand what your northing and easting parameters mean. My
experience is with headings that are 0 - 360, and north, south, etc. is
implicit in the angle.

If you need to rewrite the procedure to calculated based on different
parameters, that may be too complicated to get into.

Jerry

 

[CLE]

Jerry,
What I’m trying to do is convert latitude and longitude from
degrees/minutes/seconds to decimal format. For example, convert
(latitude/northing) 34 degrees, 57 minutes, 10 seconds SOUTH;
(longitude/easting) 58 degrees, 40 minutes, 30 seconds WEST to decimal format.

The formula is:
decimal = (Seconds/3600) + (Minutes/60)+ Degrees. If the latitude/northing
is SOUTH, the result becomes negative. If the longitude/easting is WEST, the
result becomes negative.

The function I found does the first part, [decimal = (Seconds/3600) +
(Minutes/60)+ Degrees], but it doesn’t change the result to negative if lat
is south or lon is west.

The input for the function calls for Degrees, Minutes, Seconds.

[WARNING: This custom function fails if the Degree_Deg argument does not
follow the format of the following: <degrees>o <minutes>' <seconds>"]

I think a selection for N, S, E, W could be added to the input, and the
function modified so that if that additional input is S or W, the result is
made negative. However, I am completely untrained and inexperienced writing
these things.

Thank you again!
CLE

 

[Jerry Porter]

Here's an updated version to include the direction N,S,E or W.

- I added a string parameter Direction in the function declaration
- Declared a variable DirectionFactor
- Included a set of lines to calculate the DirectionFactor as 1 or -1
- Included the DirectionFactor in the final calculation

Sample call:
?Convert_Decimal("90° 30' 0sec","s")

I used "sec" instead of " for the seconds, because it's clearer and
easier here, and the code isn't specifically looking for "

Function Convert_Decimal(Degree_Deg As String, Direction 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
Dim DirectionFactor As Integer ' 1 or -1

' 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

' Determine positive or negative based on direction
Select Case Direction
Case "N", "E"
DirectionFactor = 1
Case "S", "W"
DirectionFactor = -1
Case Else
MsgBox "Invalid direction: " & Direction
DirectionFactor = 1
End Select

Convert_Decimal = DirectionFactor * (degrees + minutes + seconds)
End Function

Jerry

Jerry,
What I'm trying to do is convert latitude and longitude from
degrees/minutes/seconds to decimal format. For example, convert
(latitude/northing) 34 degrees, 57 minutes, 10 seconds SOUTH;
(longitude/easting) 58 degrees, 40 minutes, 30 seconds WEST to decimal format.

The formula is:
decimal = (Seconds/3600) + (Minutes/60)+ Degrees. If the latitude/northing
is SOUTH, the result becomes negative. If the longitude/easting is WEST,the
result becomes negative.

The function I found does the first part, [decimal = (Seconds/3600) +
(Minutes/60)+ Degrees], but it doesn't change the result to negative if lat
is south or lon is west.

The input for the function calls for Degrees, Minutes, Seconds.

[WARNING: This custom function fails if the Degree_Deg argument does not
follow the format of the following: <degrees>o <minutes>' <seconds>"]

I think a selection for N, S, E, W could be added to the input, and the
function modified so that if that additional input is S or W, the result is
made negative. However, I am completely untrained and inexperienced writing
these things.

Thank you again!
CLE

 

[CLE]

Jerry,
It works. Thank you!
CLE

Here's an updated version to include the direction N,S,E or W.

- I added a string parameter Direction in the function declaration
- Declared a variable DirectionFactor
- Included a set of lines to calculate the DirectionFactor as 1 or -1
- Included the DirectionFactor in the final calculation

Sample call:
?Convert_Decimal("90° 30' 0sec","s")

I used "sec" instead of " for the seconds, because it's clearer and
easier here, and the code isn't specifically looking for "

Function Convert_Decimal(Degree_Deg As String, Direction 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
Dim DirectionFactor As Integer ' 1 or -1

' 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

' Determine positive or negative based on direction
Select Case Direction
Case "N", "E"
DirectionFactor = 1
Case "S", "W"
DirectionFactor = -1
Case Else
MsgBox "Invalid direction: " & Direction
DirectionFactor = 1
End Select

Convert_Decimal = DirectionFactor * (degrees + minutes + seconds)
End Function

Jerry

Jerry,
What I'm trying to do is convert latitude and longitude from
degrees/minutes/seconds to decimal format. For example, convert
(latitude/northing) 34 degrees, 57 minutes, 10 seconds SOUTH;
(longitude/easting) 58 degrees, 40 minutes, 30 seconds WEST to decimal format.

The formula is:
decimal = (Seconds/3600) + (Minutes/60)+ Degrees. If the latitude/northing
is SOUTH, the result becomes negative. If the longitude/easting is WEST, the
result becomes negative.

The function I found does the first part, [decimal = (Seconds/3600) +
(Minutes/60)+ Degrees], but it doesn't change the result to negative if lat
is south or lon is west.

The input for the function calls for Degrees, Minutes, Seconds.

[WARNING: This custom function fails if the Degree_Deg argument does not
follow the format of the following: <degrees>o <minutes>' <seconds>"]

I think a selection for N, S, E, W could be added to the input, and the
function modified so that if that additional input is S or W, the result is
made negative. However, I am completely untrained and inexperienced writing
these things.

Thank you again!
CLE