Latitude and longitude conversion in Excel

Kate A 16:57 16 Apr 2006
Locked

I'm looking for a simple formula for converting latitude and longitude references from degrees, minutes and seconds to the decimal figure.
I've googled this, and found suggestions on the Microsoft Visual Basic site, but can't get my head around how to get these to work (I thought I'd cracked it but whenever i open the xls file it just says 'VALUE!' in the boxes where the decimal degrees should be).

Any assistance gratefully received, I have an xls sheet full of co-ordinates, so need a way which converts entire columns, rather than individual locations.

thanks

VoG II 16:58 16 Apr 2006

Can you post a link to the site that tells you how to do this?

VoG II 17:08 16 Apr 2006

Kate A 17:09 16 Apr 2006

I copied the text from this into Excel (I think I did as instructed!), and it seemed to work to begin with, but now when I return to the sheet it just shows the VALUE!

VoG II 17:19 16 Apr 2006

I've just tried the MSKB method and it works fine for me (except for an initial glitch because I have Option Explicit set as a default).

If you like you can send me the workbook and I'll take a look. Click my yellow envelope to mail me, I'll reply and you'll then be able to reply with an attachment.

Kate A 17:19 16 Apr 2006

That looks to be along the right lines, but my DMS coordinates aren't in the format 45:30:30 but rather 453030 Do you know how I could convert it to 45:30:30 (i.e. just add a colon between each pair of figures)?

Kate A 17:26 16 Apr 2006

I've sent you a PM, VoG, and will be delighted if you can shed some light on this for me!

cheers!

VoG II 17:31 16 Apr 2006

I'll be happy to look at the file but I've realised that the MSKB method is doing the opposite to what you want - it is converting from decimal degrees to degrees minutes and seconds.

Converting Degrees, Minutes, And Seconds To Decimal Degrees

While it may be useful to enter degrees, minutes, and seconds in the time format, these values are not useful for computation. To convert DMS values to decimal degrees (e.g., 45:30:30 to 45.50833), multiply the DD:MM:SS value by 24, and format the cell as General. For example,
=A1*24

Noldi 17:32 16 Apr 2006

im sure if this is correct VOG would have suggested this function.
I will try and see if it works out but dont hold your breath.

Noldi

VoG II 17:34 16 Apr 2006

To convert from 453030 to 45:30:30

=LEFT(A1,2)&":"&MID(A1,3,2)&":"&RIGHT(A1,2)

assuming that all of your DMS values are 6 digits.

Kate A 17:58 16 Apr 2006

I think I've got it now, thanks VoG for your assistance.
In case you're wondering what this is for, this is the website - Adventure Ecology - click here
It's an expedition to the North Pole to encourage awareness about climate change, particularly for schools and educational institutions - click here

thanks again

Katie

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

OnePlus 5 review

See the work of famous artists playing with toys