Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 9 of 9

Thread: Convert Latitude and longitude to decimal degrees

  1. #1
    Join Date
    7th January 2011
    Posts
    4

    Convert Latitude and longitude to decimal degrees

    Hi, I am trying to convert a large number of lats & long to dec deg. My values are landing in excel in the following format in a single cell: -43 16'0.40"
    Any help with this would be much appreciated!
    Thanks in advance...
    Last edited by AAE; January 7th, 2011 at 21:02. Reason: revise thread title

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    694

    Re: Lat long conversion

    acorbett,

    Welcome to the Ozgrid forum.

    See:

    convert some latitude and longitude coordinates from "Degrees, Decimal Minutes" format to "Decimal Degrees" format
    How to convert degrees/minutes/seconds angles to or from decimal angles in Excel 2000
    http://support.microsoft.com/kb/213449


    Have a great day,
    Stan

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    7th January 2011
    Posts
    4

    Re: Lat long conversion

    Thanks Stan - trouble is I am very "green"with Excel... I get as far as the Alt F11 line to open Visual Basic Editor but nothing happens...

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    694

    Re: Lat long conversion

    acorbett,

    What version of Excel are you using?

    Please attach your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

    This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

    To attach your workbook, scroll down and click on the Go Advanced button, then scroll down and click on the Manage Attachments button.


    Have a great day,
    Stan

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    7th January 2011
    Posts
    4

    Re: Lat long conversion

    Thanks again Stan. I have attached a copy of what I have ~lats & longs (and a depth). I need to convert to decimal lats & longs (and depth) for export to some mapping sw.
    Cheers,
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Convert Latitude and longitude to decimal degrees

    acorbett,

    Please be more precise with your thread titles.

    Thread titles are used in searching the forum, therefore, it is vital the be written to accurately describe your thread content or overall objective using ONLY search friendly key words.

    • The title must not use non-essential words such as:"Help needed", "Formula problem", "Please help", "urgent", "Code issue", "Need Advice", etc. Such words dilute the title/search results.
    • The title should not contain VBA code or formula syntax or use abbreviations, jargon, delimiters
    • The title should not assume or anticipate a solution as in referencing Excel functions or VBA methods - the actual solution is often quite different


    Please note the change to your title, which is based on the objective stated in your thread.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  7. #7
    Join Date
    7th January 2011
    Posts
    4

    Re: Convert Latitude and longitude to decimal degrees

    Well please excuse my ignorance...
    How would you prefer I titled this thread???

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    12th September 2006
    Location
    The Great State of Texas
    Posts
    4,323

    Re: Convert Latitude and longitude to decimal degrees

    VB:
    Function DMS2Deg(ByVal sInp As String) As Double 
         ' Converts a string like [+|-]12 32 28" to decimal degrees
         ' The symbols don't matter; there just have to be three number groups,
         ' with one or more spaces between, optionally preceded by a sign.
         
        Dim sSgn        As Long 
        Dim i           As Long 
        Dim avs         As Variant 
         
        sSgn = IIf(Left(WorksheetFunction.Trim(sInp), 1) = "-", -1, 1) 
         
        For i = 1 To Len(sInp) 
            If InStr(" .0123456789", Mid(sInp, i, 1)) = 0 Then Mid(sInp, i) = " " 
        Next i 
         
        avs = Split(WorksheetFunction.Trim(sInp), " ") 
        DMS2Deg = sSgn * (avs(0) + avs(1) / 60# + avs(2) / 3600#) 
    End Function 
    
    
    Adding a Macro to a Code Module
    1. Copy the code from the post
    2. Press Alt+F11 to open the Visual Basic Editor (VBE)
    3. From the menu bar in the VBE window, do Insert > Module
    4. Paste the code in the window that opens
    5. Press Alt+Q to close the VBE and return to Excel

    Then with -43 16'0.40" in A1,

    =DMS2Deg(A1) returns -43.2667777777778
    Entia non sunt multiplicanda sine necessitate.
    MS MVP - Excel

  9. #9
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    694

    Re: Convert Latitude and longitude to decimal degrees

    acorbett,

    Please explain how you get the value 0.2 in cell C3?


    Have a great day and weekend,
    Stan

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Formula Conversion
    By Upside in forum EXCEL HELP
    Replies: 2
    Last Post: January 28th, 2007, 01:01
  2. Conversion
    By pbdsp in forum EXCEL HELP
    Replies: 2
    Last Post: October 14th, 2005, 00:00
  3. Automated Conversion of txt to PDF
    By juje1947 in forum Excel and/or Word Help
    Replies: 7
    Last Post: July 15th, 2005, 05:49

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
  •  
porno