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

1. ## Excel 2007 formula to convert DMS to DD (GPS coordinates)

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)?

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

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

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

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

Another way:
``Please Login or Register  to view this content.``

7. ## 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. ## 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. ## 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. ## Re: Excel 2007 formula to convert DMS to DD (GPS coordinates)

Originally Posted by Marcol
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?

11. ## 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.``

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