2020 Archives by date, by thread · List index

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