# Converting latitude longitude to decimal degrees

1. ## Converting latitude longitude to decimal degrees

I have an Excel spreadsheet with latitudes like 42° 26' 16.00" and longitudes like -123° 21' 27.00". I need to convert them to a decimal format like 43.538724 and 10.46. I'm trying to use a VB script I found, which does the conversion successfully if I manually type a latitude or longitude into a formula in a spreadsheet cell, but I don't know how to have the formula read a latitude or longitude from a cell.

The formula in the spreadsheet references the VB script. It's

=Convert_Decimal("00° 00' 00.0000""") (I have to fill in the digits.)

How can I insert a cell reference into the formula (like =Convert_Decimal("A1""")? Trying that method just produces #VALUE.

The VB script follows:

Option Explicit

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

2. ## Re: Converting latitude longitude to decimal degrees

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

3. ## Re: Converting latitude longitude to decimal degrees

Sorry, all; I should have formatted the code properly. Here it is, along with my solution to my own problem:

Option Explicit

``Please Login or Register  to view this content.``
The solution is to have the formula in the spreadsheet reference the VB code and simply point to the cell with the degrees/minutes/second-style lat/long. So the formula should be like this:

Or, in my case, =Convert_Decimal(U4)

There are currently 1 users browsing this thread. (0 members and 1 guests)