Re: [libreoffice-users] [Calc] Convert GPS coords from DMS to decimals?

At 22:11 21/11/2020 -0700, Gilles Noname wrote:
> I need to convert GPS coordinates from 
> degrees+minutes+seconds (DMS) to decimal, eg. 
> 00°05'12"O 42°59'12"N -> 42.98666667,-0.08666667
> Can Calc do this, ...

Of course!

I'm guessing that "O" means "east" (as in 
"ouest"?) and that the alternatives to "O" and 
"N" are "E" and "S" respectively?

If 00°05'12"O 42°59'12"N is in A1, then
=(MID(A1,12,2)+(MID(A1,15,2)+MID(A1,18,2)/60)/60)*((RIGHT(A1,1)="N")*2-1)
will deliver 42.9866666667, and
=(LEFT(A1,2)+(MID(A1,4,2)+MID(A1,7,2)/60)/60)*((MID(A1,10,1)="E")*2-1)
will deliver -0.0866666667.

The extracted parts of the original string are 
automatically converted from text to number on 
the fly when they are used in the arithmetical 
expressions. The equality tests in the last parts 
of the formulae are TRUE for north and east and 
FALSE for west and south. When used in an 
arithmetical expression, TRUE is interpreted as 
one and FALSE as zero. By doubling these and 
subtracting one, we get +1 for TRUE and -1 for 
FALSE, and we can multiply the calculated value 
by this number to attach the appropriate sign. 
This means that these formulae will also work for 
positions east and south of the origin - so your 
trip to the southern hemisphere will be covered.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy