+ Reply to Thread
Results 1 to 3 of 3

Converting latitude longitude to decimal degrees

  1. #1
    Registered User
    Join Date
    03-03-2012
    Location
    wardw
    MS-Off Ver
    Excel 2007
    Posts
    5

    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
    Last edited by wardw; 12-29-2012 at 03:36 PM.

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,508

    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
    I'm a newbie with PQ, so if my solution can be improved, please let me know. Thanks

  3. #3
    Registered User
    Join Date
    03-03-2012
    Location
    wardw
    MS-Off Ver
    Excel 2007
    Posts
    5

    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:

    =<Function_Name>(cell address)

    Or, in my case, =Convert_Decimal(U4)
    Last edited by wardw; 12-30-2012 at 11:47 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1