Convert Latitude and longitude to decimal degrees

[acorbett]

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

[Stanley D. Grom]

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

[acorbett]

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

[Stanley D. Grom]

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

[acorbett]

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,

[AAE]

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.

[acorbett]

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

[shg]

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

[Stanley D. Grom]

acorbett,

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


Have a great day and weekend,
Stan