+ Reply to Thread
Results 1 to 11 of 11

Excel 2007 formula to convert DMS to DD (GPS coordinates)

  1. #1
    Registered User
    Join Date
    05-26-2011
    Location
    Johnson City, TN
    MS-Off Ver
    Excel 2007
    Posts
    4

    Excel 2007 formula to convert DMS to DD (GPS coordinates)

    Please help!

    On an Excel 2007 spreadsheet I have one column for longitude and one for latitude in DDMMSS.s format (decimal minute degree).

    I need to convert these cells into DD (decimal degree) format.

    The mathematical formula is DD=D+M/60+S/3600, which for my case might look like DD+MM/60+SS.s/3600 if each digit is indicated.

    What formula can I use to apply this conversion to each column rather than each individual cell, which would take countless hours (2000+ entries)?
    Last edited by shampoo; 05-28-2011 at 12:00 AM. Reason: conforming to forum rules

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    How is your data formatted?

    If it is DD:MM:SS , you just need to multiply by 24 and format as number

    If not, please let us know how it is formatted

  3. #3
    Valued Forum Contributor Miraun's Avatar
    Join Date
    04-03-2009
    Location
    New England
    MS-Off Ver
    2003, 2007, 2010, 2013
    Posts
    554

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    ASSUMING that all of the values always contain 6 numbers for the DDMMSS latitude values, then you can use the following:

    =LEFT(A1,2)+(MID(A1,3,2)/60)+(RIGHT(A1,2)/3600)
    Where A1 is your DDMMSS value

    This takes the left 2 numbers, then 3rd and 4th numbers, and then last 2 numbers for the equation. Therefore if there is any inconsistency with the 6 numbers, then there will be problems.

  4. #4
    Registered User
    Join Date
    05-26-2011
    Location
    Johnson City, TN
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    For every cell, the data is in DDMMSS.s format, e.g. 483846.5 which would be 48 38 46.5 if spaces were included.

    That's 8 places for each value, so I assume I would just add the last two places onto the third part of the equation.

    But, will the decimal throw anything off? Every entry has a consistent decimal.

    Also, can I apply the formula to the whole column at once, without going through each cell one by one?
    Last edited by shampoo; 05-26-2011 at 03:29 PM.

  5. #5
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    Yes, you can just amend Miraun's formula to this version

    =LEFT(A1,2)+(MID(A1,3,2)/60)+(RIGHT(A1,4)/3600)

    or here's an alternative that should give the same result

    =TEXT(A1,"00\:00\:00.0")*24
    Audere est facere

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    Another way:
    Please Login or Register  to view this content.
    Entia non sunt multiplicanda sine necessitate

  7. #7
    Registered User
    Join Date
    05-26-2011
    Location
    Johnson City, TN
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    So, how do I apply this to a whole column to work for the 1000+ different entries present?

  8. #8
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    Put the formula in B1 only; then you can "fill down". One way to do that is to put the cursor on the bottom right of B1 until you see a black "+" - that's the "fill-handle". double click that and the formula will populate the whole column as far as you have continuous values in an adjacent column. The formula automatically adjusts to refer to the cell on that row

  9. #9
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    Hmm?

    As a matter of interest this thread began with somerthing like this as co-ordinates
    N48-38-46.5, E088-19-14.4
    Which turns out to be a filling station "near" a military base in western Mongolia.

    How come the entire post (Post #1) changed?

    Or was I dreaming?

  10. #10
    Registered User
    Join Date
    05-26-2011
    Location
    Johnson City, TN
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    Quote Originally Posted by Marcol View Post
    Hmm?

    As a matter of interest this thread began with somerthing like this as co-ordinates
    N48-38-46.5, E088-19-14.4
    Which turns out to be a filling station "near" a military base in western Mongolia.

    How come the entire post (Post #1) changed?

    Or was I dreaming?
    Conducting research near a military base in Mongolia. Nothing confidential. I edited the original post because I had changed things on my excel sheet to make all this easier.

    Thanks everyone!

    I have one last question: When I try to extend daddylonglegs' formula to an entire column, it's only changing the first value LEFT(A1,2) to the proper cell value, whereas MID and RIGHT are stuck on the first entry. Any last tips?
    Last edited by shampoo; 05-26-2011 at 09:44 PM.

  11. #11
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

    So this, that I had done and didn't post because I was waiting for you to respond to arthurbrs' request, was a complete waste of time?
    Please Login or Register  to view this content.
    Please Login or Register  to view this content.

    Formatted > Custom
    Please Login or Register  to view this content.


    [EDIT]
    Not a complete waste of time

    This also works
    Please Login or Register  to view this content.
    Thanks to daddylonglegs
    Last edited by Marcol; 05-27-2011 at 04:14 AM.
    If you need any more information, please feel free to ask.

    However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....

    Also
    اس کی مدد کرتا ہے اگر
    شکریہ کہنے کے لئے سٹار کلک کریں
    If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.

+ 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