# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  Excel 2007 formula to convert DMS to DD (GPS coordinates)

## shampoo

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

----------


## arthurbr

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

----------


## Miraun

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.

----------


## shampoo

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?

----------


## daddylonglegs

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

----------


## shg

Another way:



```
Please Login or Register  to view this content.
```

----------


## shampoo

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

----------


## daddylonglegs

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

----------


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

----------


## shampoo

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

----------


## Marcol

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

----------

