Announcement

Collapse
No announcement yet.

Convert Latitude and longitude to decimal degrees

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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, 21:02. Reason: revise thread title

  • #2
    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

    Comment


    • #3
      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...

      Comment


      • #4
        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

        Comment


        • #5
          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

          Comment


          • #6
            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

            Comment


            • #7
              Re: Convert Latitude and longitude to decimal degrees

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

              Comment


              • #8
                Re: Convert Latitude and longitude to decimal degrees

                Code:
                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.

                Comment


                • #9
                  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

                  Comment

                  Working...
                  X