[Calc] Convert GPS coords from DMS to decimals?

classic Classic list List threaded Threaded
21 messages Options
Next » 12
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

[Calc] Convert GPS coords from DMS to decimals?

Hello,

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

https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>  

Can Calc do this, or should I look elsewhere?

Thank you.



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
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
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

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

Would need more info on the format, but the following calculation
seems to work to convert the number part. Not sure of the N and
O, or how - (negative sign) in some work...

Did the following.
Put in Cell A1: 00°05'12"O 42°59'12"N -> 42.98666667,-0.08666667
In Cell B1: Put the Formula -
=VALUE(MID(A1,1,2))+VALUE(MID(A1,4,2))/60+VALUE(MID(A1,7,2))/3600
That gives result of 0.086666666666667
In Cell C1: Put the Forumula -
=VALUE(MID(A1,12,2))+VALUE(MID(A1,15,2))/60+VALUE(MID(A1,18,2))/3
600
That gives result of 42.9866666666667
So, B1 and C1 are the right numbers kind of, reversed order and
one should be negative.

So might require some additional work, but the math seems
simple.

On 21 Nov 2020 at 22:11, Gilles wrote:

Date sent:       Sat, 21 Nov 2020 22:11:59 -0700 (MST)
From:           Gilles <[hidden email]>
To:             [hidden email]
Subject:         [libreoffice-users] [Calc] Convert GPS coords
from DMS to decimals?

> Hello,
>
> 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
>
> https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>  
>
> Can Calc do this, or should I look elsewhere?
>
> Thank you.
>
>
>
> --
> Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
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: [hidden email]
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

Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
Yes, it's very, very easy (when you know how to do it…). Those coordinates
work exactly like time, so all you need to do is to format your input cells
properly (if you care about looks) and multiply your input cells with 24
(hours per day) in your output cells, because when working with time in
Calc (or Excel or any other spreadsheet application), the result is in
days, so 0,5 (or 0.5 if you use a period for the decimal symbol) means
12:00:00, 0,75 is 18:00:00 and so on.

Follow this for a demo:

   1. Highlight A1 and right click and click ”Format cells…”.
   2. Click the ”Numbers” tab.
   3. In the ”Category” field, select Time and in the format Field select
   the line that looks something like ”13:37:46”.
   4. Now, in the ”Format code” field, replace the colons (or whatever they
   are in your case; it's language dependent) with degrees and the other
   characters inside double quotes, and also make sure your hours symbol is
   inside [], which means it won't flip over to 0 for greater numbers than 23.
   In my case (Swedish), it looks like: "N"[TT]"°"MM"'"SS""". If English
   (USA): "N"[HH]"°"MM"'"SS""". There is a field right under the Language
   selection that gives you an example of what the result would look like. In
   my case it reads: N13°37'46".
   5. Hit ”OK”.
   6. Repeat steps 1 to 5, but highlight B1 instead of A1 and replace "N"
   with "E" in the ”Format code” field.
   7. In A2, type: =A1*24
   8. In B2, type: =B1*24 (or just auto-fill from A2 or even copy and paste
   A2 to B2)
   9. Highlight A2:B2 and increase the number of decimals using the ”.00+”
   button or do it in the ”Format cells…” dialogue as before by entering
   something like 0,0000000 in the ”Format code” field (or 0.0000000 if your
   decimal symbol is a period).
   10. Now, in A1, type:
   42:59:12.
   Remember to treat the number as time rather than coordinates. Replace
   ”:” with whatever is the appropriate time separator for your language.
   11. In B1, type:
   0:5:12

When following my own instructions, here's what my cells look like:
A1
N42°59'12"
B1
E00°05'12"
A2
42,9866666666667
B2
0,086666666666667

You could of course put the both together to a complete text string, but
then you can't easily use them for further calculations. For instance, in
A3, type (for a result with 8 decimals):
=ROUND(A1*24;8) & ", " & ROUND(B1*24;8)
or, if you want to use the values in A2 and B2:
=ROUND(A2;8) & ", " & ROUND(B2;8)

Result (in my case):
42,98666667, 0,08666667

So, as you see, no advanced formulas are needed at all.

I hope there were not too many typos above.


Kind regards

Johnny Rosenberg


Den sön 22 nov. 2020 kl 06:14 skrev Gilles <[hidden email]>:

> Hello,
>
> 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
>
> https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>
>
> Can Calc do this, or should I look elsewhere?
>
> Thank you.
>
>
>
> --
> Sent from:
> http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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
>

--
To unsubscribe e-mail to: [hidden email]
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
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

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

Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg <[hidden email]
>:

> Yes, it's very, very easy (when you know how to do it…). Those coordinates
> work exactly like time, so all you need to do is to format your input cells
> properly (if you care about looks) and multiply your input cells with 24
> (hours per day) in your output cells, because when working with time in
> Calc (or Excel or any other spreadsheet application), the result is in
> days, so 0,5 (or 0.5 if you use a period for the decimal symbol) means
> 12:00:00, 0,75 is 18:00:00 and so on.
>
> Follow this for a demo:
>
>    1. Highlight A1 and right click and click ”Format cells…”.
>    2. Click the ”Numbers” tab.
>    3. In the ”Category” field, select Time and in the format Field select
>    the line that looks something like ”13:37:46”.
>    4. Now, in the ”Format code” field, replace the colons (or whatever
>    they are in your case; it's language dependent) with degrees and the other
>    characters inside double quotes, and also make sure your hours symbol is
>    inside [], which means it won't flip over to 0 for greater numbers than 23.
>    In my case (Swedish), it looks like: "N"[TT]"°"MM"'"SS""". If English
>    (USA): "N"[HH]"°"MM"'"SS""". There is a field right under the Language
>    selection that gives you an example of what the result would look like. In
>    my case it reads: N13°37'46".
>    5. Hit ”OK”.
>    6. Repeat steps 1 to 5, but highlight B1 instead of A1 and replace "N"
>    with "E" in the ”Format code” field.
>    7. In A2, type: =A1*24
>    8. In B2, type: =B1*24 (or just auto-fill from A2 or even copy and
>    paste A2 to B2)
>    9. Highlight A2:B2 and increase the number of decimals using the
>    ”.00+” button or do it in the ”Format cells…” dialogue as before by
>    entering something like 0,0000000 in the ”Format code” field (or 0.0000000
>    if your decimal symbol is a period).
>    10. Now, in A1, type:
>    42:59:12.
>    Remember to treat the number as time rather than coordinates. Replace
>    ”:” with whatever is the appropriate time separator for your language.
>    11. In B1, type:
>    0:5:12
>
> I just read your question again and found that you had it the other way
around (east-west first and then north-south and using O instead of E), so
in your case then:
A1 format code: [HH]"°"MM"'"SS""""O"
B1 format code: [HH]"°"MM"'"SS""""N"
But this won't work, since Calc is not able to figure out all those double
quotes correctly, so my workaround is to use the ” double quote instead
(you can copy it from here, if you like, otherwise the UNICODE code is
U+201D. To match that I also use the corresponding ’ single quote, that is
U+2019, so in this case:
A1 format code: [HH]"°"MM"’"SS"”O"
B1 format code: [HH]"°"MM"’"SS"”N"
You can copy the whole thing from above, of course (and then replace the
letters to what's correct in your selected language).


A2=B1*24
B2=A1*24

The rest should be the same, I guess.



> When following my own instructions, here's what my cells look like:
> A1
> N42°59'12"
> B1
> E00°05'12"
> A2
> 42,9866666666667
> B2
> 0,086666666666667
>
> You could of course put the both together to a complete text string, but
> then you can't easily use them for further calculations. For instance, in
> A3, type (for a result with 8 decimals):
> =ROUND(A1*24;8) & ", " & ROUND(B1*24;8)
>
=ROUND(B1*24;8) & ", " & ROUND(A1*24;8)

> or, if you want to use the values in A2 and B2:
> =ROUND(A2;8) & ", " & ROUND(B2;8)
>

And you can, of course, also add the degree symbol if you like:
=ROUND(A1*24;8) & "°, " & ROUND(B1*24;8) & "°"


> Result (in my case):
> 42,98666667, 0,08666667
>

42,98666667°, 0,08666667° after adding the degree symbols.


> So, as you see, no advanced formulas are needed at all.
>
Still correct. ☺

>
> I hope there were not too many typos above.
>
>
> Kind regards
>
> Johnny Rosenberg
>
>
> Den sön 22 nov. 2020 kl 06:14 skrev Gilles <[hidden email]>:
>
>> Hello,
>>
>> 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
>>
>> https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>
>>
>> Can Calc do this, or should I look elsewhere?
>>
>> Thank you.
>>
>>
>>
>> --
>> Sent from:
>> http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
>>
>> --
>> To unsubscribe e-mail to: [hidden email]
>> 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
>>
>

--
To unsubscribe e-mail to: [hidden email]
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
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

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

Ha ha ha… this time I also looked at your original link. The image there
uses both E and O. Do they mean the same or else, what do they mean? Is E
for East and O for West or maybe the other way around? Anyway, in my
examples, just input a positive number for East and a negative number for
West and replace the O in my example with whatever means East.
Same goes for North and South, of course. A negative number means south, a
positive number means north. Replace the N in my example with whatever
means North in your language.



Kind regards

Johnny Rosenberg

Den sön 22 nov. 2020 kl 12:23 skrev Johnny Rosenberg <[hidden email]
>:

> Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg <
> [hidden email]>:
>
>> Yes, it's very, very easy (when you know how to do it…). Those
>> coordinates work exactly like time, so all you need to do is to format your
>> input cells properly (if you care about looks) and multiply your input
>> cells with 24 (hours per day) in your output cells, because when working
>> with time in Calc (or Excel or any other spreadsheet application), the
>> result is in days, so 0,5 (or 0.5 if you use a period for the decimal
>> symbol) means 12:00:00, 0,75 is 18:00:00 and so on.
>>
>> Follow this for a demo:
>>
>>    1. Highlight A1 and right click and click ”Format cells…”.
>>    2. Click the ”Numbers” tab.
>>    3. In the ”Category” field, select Time and in the format Field
>>    select the line that looks something like ”13:37:46”.
>>    4. Now, in the ”Format code” field, replace the colons (or whatever
>>    they are in your case; it's language dependent) with degrees and the other
>>    characters inside double quotes, and also make sure your hours symbol is
>>    inside [], which means it won't flip over to 0 for greater numbers than 23.
>>    In my case (Swedish), it looks like: "N"[TT]"°"MM"'"SS""". If English
>>    (USA): "N"[HH]"°"MM"'"SS""". There is a field right under the Language
>>    selection that gives you an example of what the result would look like. In
>>    my case it reads: N13°37'46".
>>    5. Hit ”OK”.
>>    6. Repeat steps 1 to 5, but highlight B1 instead of A1 and replace
>>    "N" with "E" in the ”Format code” field.
>>    7. In A2, type: =A1*24
>>    8. In B2, type: =B1*24 (or just auto-fill from A2 or even copy and
>>    paste A2 to B2)
>>    9. Highlight A2:B2 and increase the number of decimals using the
>>    ”.00+” button or do it in the ”Format cells…” dialogue as before by
>>    entering something like 0,0000000 in the ”Format code” field (or 0.0000000
>>    if your decimal symbol is a period).
>>    10. Now, in A1, type:
>>    42:59:12.
>>    Remember to treat the number as time rather than coordinates. Replace
>>    ”:” with whatever is the appropriate time separator for your language.
>>    11. In B1, type:
>>    0:5:12
>>
>> I just read your question again and found that you had it the other way
> around (east-west first and then north-south and using O instead of E), so
> in your case then:
> A1 format code: [HH]"°"MM"'"SS""""O"
> B1 format code: [HH]"°"MM"'"SS""""N"
> But this won't work, since Calc is not able to figure out all those double
> quotes correctly, so my workaround is to use the ” double quote instead
> (you can copy it from here, if you like, otherwise the UNICODE code is
> U+201D. To match that I also use the corresponding ’ single quote, that is
> U+2019, so in this case:
> A1 format code: [HH]"°"MM"’"SS"”O"
> B1 format code: [HH]"°"MM"’"SS"”N"
> You can copy the whole thing from above, of course (and then replace the
> letters to what's correct in your selected language).
>
>
> A2=B1*24
> B2=A1*24
>
> The rest should be the same, I guess.
>
>
>
>> When following my own instructions, here's what my cells look like:
>> A1
>> N42°59'12"
>> B1
>> E00°05'12"
>> A2
>> 42,9866666666667
>> B2
>> 0,086666666666667
>>
>> You could of course put the both together to a complete text string, but
>> then you can't easily use them for further calculations. For instance, in
>> A3, type (for a result with 8 decimals):
>> =ROUND(A1*24;8) & ", " & ROUND(B1*24;8)
>>
> =ROUND(B1*24;8) & ", " & ROUND(A1*24;8)
>
>> or, if you want to use the values in A2 and B2:
>> =ROUND(A2;8) & ", " & ROUND(B2;8)
>>
>
> And you can, of course, also add the degree symbol if you like:
> =ROUND(A1*24;8) & "°, " & ROUND(B1*24;8) & "°"
>
>
>> Result (in my case):
>> 42,98666667, 0,08666667
>>
>
> 42,98666667°, 0,08666667° after adding the degree symbols.
>
>
>> So, as you see, no advanced formulas are needed at all.
>>
> Still correct. ☺
>
>>
>> I hope there were not too many typos above.
>>
>>
>> Kind regards
>>
>> Johnny Rosenberg
>>
>>
>> Den sön 22 nov. 2020 kl 06:14 skrev Gilles <[hidden email]>:
>>
>>> Hello,
>>>
>>> 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
>>>
>>> https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>
>>>
>>> Can Calc do this, or should I look elsewhere?
>>>
>>> Thank you.
>>>
>>>
>>>
>>> --
>>> Sent from:
>>> http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
>>>
>>> --
>>> To unsubscribe e-mail to: [hidden email]
>>> 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
>>>
>>

--
To unsubscribe e-mail to: [hidden email]
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
Johnny Rosenberg Johnny Rosenberg
Reply | Threaded
Open this post in threaded view
|

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

And this is the fourth and last reply (I hope), unless there are follow-up
questions…

I just realised that there are actually dedicated UNICODE characters for
minutes and seconds, and they are U+2032 for minutes (and also feet), and
U+2033 for seconds (and also inches), so this would probably be more
correct:
[TT]"°"MM"′"SS"″O"
[TT]"°"MM"′"SS"″N"

Result:
00°05′12″O
42°58′12″N

And yhou can have decimals for your seconds too, of course:
[TT]"°"MM"′"SS,00"″O"
[TT]"°"MM"′"SS,00"″N"

Or, if a period is used for decimals in your language:
[TT]"°"MM"′"SS.00"″O"
[TT]"°"MM"′"SS.00"″N"

Result in my case, after inputting 0:5:15,53 and 42:48:12,8:
00°05′12,53″O 42°58′12,80″N
42,97022222°, 0,08681389°



Kind regards

Johnny Rosenberg


Den sön 22 nov. 2020 kl 12:30 skrev Johnny Rosenberg <[hidden email]
>:

> Ha ha ha… this time I also looked at your original link. The image there
> uses both E and O. Do they mean the same or else, what do they mean? Is E
> for East and O for West or maybe the other way around? Anyway, in my
> examples, just input a positive number for East and a negative number for
> West and replace the O in my example with whatever means East.
> Same goes for North and South, of course. A negative number means south, a
> positive number means north. Replace the N in my example with whatever
> means North in your language.
>
>
>
> Kind regards
>
> Johnny Rosenberg
>
> Den sön 22 nov. 2020 kl 12:23 skrev Johnny Rosenberg <
> [hidden email]>:
>
>> Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg <
>> [hidden email]>:
>>
>>> Yes, it's very, very easy (when you know how to do it…). Those
>>> coordinates work exactly like time, so all you need to do is to format your
>>> input cells properly (if you care about looks) and multiply your input
>>> cells with 24 (hours per day) in your output cells, because when working
>>> with time in Calc (or Excel or any other spreadsheet application), the
>>> result is in days, so 0,5 (or 0.5 if you use a period for the decimal
>>> symbol) means 12:00:00, 0,75 is 18:00:00 and so on.
>>>
>>> Follow this for a demo:
>>>
>>>    1. Highlight A1 and right click and click ”Format cells…”.
>>>    2. Click the ”Numbers” tab.
>>>    3. In the ”Category” field, select Time and in the format Field
>>>    select the line that looks something like ”13:37:46”.
>>>    4. Now, in the ”Format code” field, replace the colons (or whatever
>>>    they are in your case; it's language dependent) with degrees and the other
>>>    characters inside double quotes, and also make sure your hours symbol is
>>>    inside [], which means it won't flip over to 0 for greater numbers than 23.
>>>    In my case (Swedish), it looks like: "N"[TT]"°"MM"'"SS""". If English
>>>    (USA): "N"[HH]"°"MM"'"SS""". There is a field right under the Language
>>>    selection that gives you an example of what the result would look like. In
>>>    my case it reads: N13°37'46".
>>>    5. Hit ”OK”.
>>>    6. Repeat steps 1 to 5, but highlight B1 instead of A1 and replace
>>>    "N" with "E" in the ”Format code” field.
>>>    7. In A2, type: =A1*24
>>>    8. In B2, type: =B1*24 (or just auto-fill from A2 or even copy and
>>>    paste A2 to B2)
>>>    9. Highlight A2:B2 and increase the number of decimals using the
>>>    ”.00+” button or do it in the ”Format cells…” dialogue as before by
>>>    entering something like 0,0000000 in the ”Format code” field (or 0.0000000
>>>    if your decimal symbol is a period).
>>>    10. Now, in A1, type:
>>>    42:59:12.
>>>    Remember to treat the number as time rather than coordinates.
>>>    Replace ”:” with whatever is the appropriate time separator for your
>>>    language.
>>>    11. In B1, type:
>>>    0:5:12
>>>
>>> I just read your question again and found that you had it the other way
>> around (east-west first and then north-south and using O instead of E), so
>> in your case then:
>> A1 format code: [HH]"°"MM"'"SS""""O"
>> B1 format code: [HH]"°"MM"'"SS""""N"
>> But this won't work, since Calc is not able to figure out all those
>> double quotes correctly, so my workaround is to use the ” double quote
>> instead (you can copy it from here, if you like, otherwise the UNICODE code
>> is U+201D. To match that I also use the corresponding ’ single quote, that
>> is U+2019, so in this case:
>> A1 format code: [HH]"°"MM"’"SS"”O"
>> B1 format code: [HH]"°"MM"’"SS"”N"
>> You can copy the whole thing from above, of course (and then replace the
>> letters to what's correct in your selected language).
>>
>>
>> A2=B1*24
>> B2=A1*24
>>
>> The rest should be the same, I guess.
>>
>>
>>
>>> When following my own instructions, here's what my cells look like:
>>> A1
>>> N42°59'12"
>>> B1
>>> E00°05'12"
>>> A2
>>> 42,9866666666667
>>> B2
>>> 0,086666666666667
>>>
>>> You could of course put the both together to a complete text string, but
>>> then you can't easily use them for further calculations. For instance, in
>>> A3, type (for a result with 8 decimals):
>>> =ROUND(A1*24;8) & ", " & ROUND(B1*24;8)
>>>
>> =ROUND(B1*24;8) & ", " & ROUND(A1*24;8)
>>
>>> or, if you want to use the values in A2 and B2:
>>> =ROUND(A2;8) & ", " & ROUND(B2;8)
>>>
>>
>> And you can, of course, also add the degree symbol if you like:
>> =ROUND(A1*24;8) & "°, " & ROUND(B1*24;8) & "°"
>>
>>
>>> Result (in my case):
>>> 42,98666667, 0,08666667
>>>
>>
>> 42,98666667°, 0,08666667° after adding the degree symbols.
>>
>>
>>> So, as you see, no advanced formulas are needed at all.
>>>
>> Still correct. ☺
>>
>>>
>>> I hope there were not too many typos above.
>>>
>>>
>>> Kind regards
>>>
>>> Johnny Rosenberg
>>>
>>>
>>> Den sön 22 nov. 2020 kl 06:14 skrev Gilles <[hidden email]>:
>>>
>>>> Hello,
>>>>
>>>> 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
>>>>
>>>> https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>
>>>>
>>>> Can Calc do this, or should I look elsewhere?
>>>>
>>>> Thank you.
>>>>
>>>>
>>>>
>>>> --
>>>> Sent from:
>>>> http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
>>>>
>>>> --
>>>> To unsubscribe e-mail to: [hidden email]
>>>> 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
>>>>
>>>

--
To unsubscribe e-mail to: [hidden email]
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
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Brian Barker
Thanks everyone for the input.

Using regexes, I split the latitudes and longitudes so they're in their own
columns.


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

Sorry, I didn't pay attention to that part.

O = ouest = West
E = est = East


Brian Barker wrote
> 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.

I tried this formula, but get error 509 ("operator expected"). Maybe it's
because the cell contains "Numbers"?

https://postimg.cc/w764P7Rm <https://postimg.cc/w764P7Rm>  



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
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
Philip Jackson-2 Philip Jackson-2
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Johnny Rosenberg
On 22/11/2020 12:30, Johnny Rosenberg wrote:
> Ha ha ha… this time I also looked at your original link. The image there
> uses both E and O. Do they mean the same or else, what do they mean? Is E
> for East and O for West or maybe the other way around? Anyway, in my
> examples, just input a positive number for East and a negative number for
> West and replace the O in my example with whatever means East.
> Same goes for North and South, of course. A negative number means south, a
> positive number means north. Replace the N in my example with whatever
> means North in your language.

In French : N nord = north, S  sud = south, O ouest = west, E est = east

Philip


--
To unsubscribe e-mail to: [hidden email]
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
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
Gilles wrote
> I tried this formula, but get error 509 ("operator expected"). Maybe it's
> because the cell contains "Numbers"?

I get the same error even after reformatting the data as XX:XX:XX, and
changing the column from Number to Time:

https://postimg.cc/D43Hm6zj <https://postimg.cc/D43Hm6zj>  



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
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

Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

Gilles wrote
> I get the same error even after reformatting the data as XX:XX:XX, and
> changing the column from Number to Time:

… and yet again after fixing the typo (A1 → D1):

https://postimg.cc/VdkQt7Rp <https://postimg.cc/VdkQt7Rp>  



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
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
Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Johnny Rosenberg
Time conversion of input makes the calculation simpler, but
doesn't handle the values that should be negative. Also, some of
the sample values had 3 digit values, but when entered as time
value it adjust values?

I worked with it uses the values as strings as was shown.

00°05'12"O 42°59'12"N 00°05'12"O 42°59'12"N
        -0.086666666666667 42.9866666666667
03°15'090"E 43°12'814"N 03°15'090"E 43°12'814"N
        3.275 43.4261111111111

Column A has the original formatted examples of data.
Column B pulls the first value
=LEFT(INDIRECT(ADDRESS(ROW(),COLUMN()-1)),FIND("
",INDIRECT(ADDRESS(ROW(),COLUMN()-1)))-1)
Column C pulls the second value
=MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),FIND("
",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))+1,20)
Column D converts value in Column B
=(VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),
1,FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-1))
+VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),
FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))+1,FI
ND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-
FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-1))/6
0+VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2))
,FIND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLUMN()-2
)))+1,FIND(CHAR(34),INDIRECT(ADDRESS(ROW(),COLUM
N()-2)))-FIND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLU
MN()-2)))-1))/3600)*IF(OR(RIGHT(INDIRECT(ADDRESS(RO
W(),COLUMN()-2)),1)="E",RIGHT(INDIRECT(ADDRESS(RO
W(),COLUMN()-2)),1)="N"),1,-1)
Column E converts value in Column C
=(VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),
1,FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-1))
+VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),
FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))+1,FI
ND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-
FIND("°",INDIRECT(ADDRESS(ROW(),COLUMN()-2)))-1))/6
0+VALUE(MID(INDIRECT(ADDRESS(ROW(),COLUMN()-2))
,FIND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLUMN()-2
)))+1,FIND(CHAR(34),INDIRECT(ADDRESS(ROW(),COLUM
N()-2)))-FIND(CHAR(39),INDIRECT(ADDRESS(ROW(),COLU
MN()-2)))-1))/3600)*IF(OR(RIGHT(INDIRECT(ADDRESS(RO
W(),COLUMN()-2)),1)="E",RIGHT(INDIRECT(ADDRESS(RO
W(),COLUMN()-2)),1)="N"),1,-1)

Created a macro that automatically does this. Have value in
Column A in the text format, and run macro in column B. It then
does all the formulas.
It does create the negative values if values are not N or E..

Converting the data in column A to Time format is interesting.

Noticed some of values have 3 digit values.
43°12'814"N
When one enters it as time, it changes it to
43°25'34"
Not sure if they are equivalent, or if the original data was in error.

Macro was a pain to create. If values would also be fixed 2 digit
numbers, it is also much simpler, since no need for find..

Interesting to play with...


On 22 Nov 2020 at 12:43, Johnny Rosenberg wrote:

From:           Johnny Rosenberg
<[hidden email]>
Date sent:       Sun, 22 Nov 2020 12:43:35 +0100
Subject:         Re: [libreoffice-users] [Calc] Convert GPS
coords from DMS to
        decimals?
To:             LibreOffice Användare
<[hidden email]>

> And this is the fourth and last reply (I hope), unless there are follow-up
> questions...
>
> I just realised that there are actually dedicated UNICODE characters for
> minutes and seconds, and they are U+2032 for minutes (and also feet), and
> U+2033 for seconds (and also inches), so this would probably be more
> correct:
> [TT]"°"MM"´"SS"´´O"
> [TT]"°"MM"´"SS"´´N"
>
> Result:
> 00°05´12´´O
> 42°58´12´´N
>
> And yhou can have decimals for your seconds too, of course:
> [TT]"°"MM"´"SS,00"´´O"
> [TT]"°"MM"´"SS,00"´´N"
>
> Or, if a period is used for decimals in your language:
> [TT]"°"MM"´"SS.00"´´O"
> [TT]"°"MM"´"SS.00"´´N"
>
> Result in my case, after inputting 0:5:15,53 and 42:48:12,8:
> 00°05´12,53´´O 42°58´12,80´´N
> 42,97022222°, 0,08681389°
>
>
>
> Kind regards
>
> Johnny Rosenberg
>
>
> Den sön 22 nov. 2020 kl 12:30 skrev Johnny Rosenberg <[hidden email]
> >:
>
> > Ha ha ha... this time I also looked at your original link. The image there
> > uses both E and O. Do they mean the same or else, what do they mean? Is E
> > for East and O for West or maybe the other way around? Anyway, in my
> > examples, just input a positive number for East and a negative number for
> > West and replace the O in my example with whatever means East.
> > Same goes for North and South, of course. A negative number means south, a
> > positive number means north. Replace the N in my example with whatever
> > means North in your language.
> >
> >
> >
> > Kind regards
> >
> > Johnny Rosenberg
> >
> > Den sön 22 nov. 2020 kl 12:23 skrev Johnny Rosenberg <
> > [hidden email]>:
> >
> >> Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg <
> >> [hidden email]>:
> >>
> >>> Yes, it's very, very easy (when you know how to do it...). Those
> >>> coordinates work exactly like time, so all you need to do is to format your
> >>> input cells properly (if you care about looks) and multiply your input
> >>> cells with 24 (hours per day) in your output cells, because when working
> >>> with time in Calc (or Excel or any other spreadsheet application), the
> >>> result is in days, so 0,5 (or 0.5 if you use a period for the decimal
> >>> symbol) means 12:00:00, 0,75 is 18:00:00 and so on.
> >>>
> >>> Follow this for a demo:
> >>>
> >>>    1. Highlight A1 and right click and click "Format cells...".
> >>>    2. Click the "Numbers" tab.
> >>>    3. In the "Category" field, select Time and in the format Field
> >>>    select the line that looks something like "13:37:46".
> >>>    4. Now, in the "Format code" field, replace the colons (or whatever
> >>>    they are in your case; it's language dependent) with degrees and the other
> >>>    characters inside double quotes, and also make sure your hours symbol is
> >>>    inside [], which means it won't flip over to 0 for greater numbers than 23.
> >>>    In my case (Swedish), it looks like: "N"[TT]"°"MM"'"SS""". If English
> >>>    (USA): "N"[HH]"°"MM"'"SS""". There is a field right under the Language
> >>>    selection that gives you an example of what the result would look like. In
> >>>    my case it reads: N13°37'46".
> >>>    5. Hit "OK".
> >>>    6. Repeat steps 1 to 5, but highlight B1 instead of A1 and replace
> >>>    "N" with "E" in the "Format code" field.
> >>>    7. In A2, type: =A1*24
> >>>    8. In B2, type: =B1*24 (or just auto-fill from A2 or even copy and
> >>>    paste A2 to B2)
> >>>    9. Highlight A2:B2 and increase the number of decimals using the
> >>>    ".00+" button or do it in the "Format cells..." dialogue as before by
> >>>    entering something like 0,0000000 in the "Format code" field (or 0.0000000
> >>>    if your decimal symbol is a period).
> >>>    10. Now, in A1, type:
> >>>    42:59:12.
> >>>    Remember to treat the number as time rather than coordinates.
> >>>    Replace ":" with whatever is the appropriate time separator for your
> >>>    language.
> >>>    11. In B1, type:
> >>>    0:5:12
> >>>
> >>> I just read your question again and found that you had it the other way
> >> around (east-west first and then north-south and using O instead of E), so
> >> in your case then:
> >> A1 format code: [HH]"°"MM"'"SS""""O"
> >> B1 format code: [HH]"°"MM"'"SS""""N"
> >> But this won't work, since Calc is not able to figure out all those
> >> double quotes correctly, so my workaround is to use the " double quote
> >> instead (you can copy it from here, if you like, otherwise the UNICODE code
> >> is U+201D. To match that I also use the corresponding ´ single quote, that
> >> is U+2019, so in this case:
> >> A1 format code: [HH]"°"MM"´"SS""O"
> >> B1 format code: [HH]"°"MM"´"SS""N"
> >> You can copy the whole thing from above, of course (and then replace the
> >> letters to what's correct in your selected language).
> >>
> >>
> >> A2=B1*24
> >> B2=A1*24
> >>
> >> The rest should be the same, I guess.
> >>
> >>
> >>
> >>> When following my own instructions, here's what my cells look like:
> >>> A1
> >>> N42°59'12"
> >>> B1
> >>> E00°05'12"
> >>> A2
> >>> 42,9866666666667
> >>> B2
> >>> 0,086666666666667
> >>>
> >>> You could of course put the both together to a complete text string, but
> >>> then you can't easily use them for further calculations. For instance, in
> >>> A3, type (for a result with 8 decimals):
> >>> =ROUND(A1*24;8) & ", " & ROUND(B1*24;8)
> >>>
> >> =ROUND(B1*24;8) & ", " & ROUND(A1*24;8)
> >>
> >>> or, if you want to use the values in A2 and B2:
> >>> =ROUND(A2;8) & ", " & ROUND(B2;8)
> >>>
> >>
> >> And you can, of course, also add the degree symbol if you like:
> >> =ROUND(A1*24;8) & "°, " & ROUND(B1*24;8) & "°"
> >>
> >>
> >>> Result (in my case):
> >>> 42,98666667, 0,08666667
> >>>
> >>
> >> 42,98666667°, 0,08666667° after adding the degree symbols.
> >>
> >>
> >>> So, as you see, no advanced formulas are needed at all.
> >>>
> >> Still correct.
> >>
> >>>
> >>> I hope there were not too many typos above.
> >>>
> >>>
> >>> Kind regards
> >>>
> >>> Johnny Rosenberg
> >>>
> >>>
> >>> Den sön 22 nov. 2020 kl 06:14 skrev Gilles <[hidden email]>:
> >>>
> >>>> Hello,
> >>>>
> >>>> 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
> >>>>
> >>>> https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>
> >>>>
> >>>> Can Calc do this, or should I look elsewhere?
> >>>>
> >>>> Thank you.
> >>>>
> >>>>
> >>>>
> >>>> --
> >>>> Sent from:
> >>>> http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html
> >>>>
> >>>> --
> >>>> To unsubscribe e-mail to: [hidden email]
> >>>> 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
> >>>>
> >>>
>
> --
> To unsubscribe e-mail to: [hidden email]
> 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


+------------------------------------------------------------+
 Michael D. Setzer II - Computer Science Instructor (Retired)    
 mailto:[hidden email]                            
 mailto:[hidden email]
 Guam - Where America's Day Begins                        
 G4L Disk Imaging Project maintainer
 http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+




--
To unsubscribe e-mail to: [hidden email]
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

Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

Thank you.

The issue with Error 509 was apparently due to columns being of the wrong
type (source should be text, and target should be number), and using ","
instead of ";" in Left() and Mid().

After copy/pasting into/from Notepad, it's working… but the West/East isn't:
Even when LO finds "O" ("ouest" for West) in the source, I'm not getting a
negative as expected:

https://postimg.cc/V0MgT0X3 <https://postimg.cc/V0MgT0X3>  



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
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
Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

Problem solved by copy/pasting into a text colum, manually selecting the
relevant cells, and running a regex to prepend a minus sign. Not proud of
this, but it got the job done.

Thanks everyone for the help.

https://postimg.cc/6yVT0V8C <https://postimg.cc/6yVT0V8C>  



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
At 05:20 22/11/2020 -0700, Gilles Noname wrote:
>Brian Barker wrote
>>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.
>
>I tried this formula, but get error 509
>("operator expected"). Maybe it's because the cell contains "Numbers"?

No, that's not what error 509 means.

>https://postimg.cc/w764P7Rm

You've shown us the error message, but with focus
on a different cell, not showing the error. So
no-one can see the erroneous formula! I'm
guessing that your locale needs semicolons as
separators in place of the commas.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
At 06:48 22/11/2020 -0700, Gilles Noname wrote:
>The issue with Error 509 was apparently due to
>columns being of the wrong type (source should
>be text, and target should be number), ...

No, it will not be that.

>... and using "," instead of ";" in Left() and Mid().

Yes, if your locale needs semicolons as
separators commas will not do. That is definitely
what error 509 will be signalling.

>... it's working… but the West/East isn't: Even
>when LO finds "O" ("ouest" for West) in the
>source, I'm not getting a negative as expected:

That is because you have - weirdly - chosen to
mess up my formula, changing my "E" (for east)
into "O" (for west). So *of course* the longitude
signs are now reversed. Reinstate my "E" and everything will work.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
At 07:40 22/11/2020 -0700, Gilles Noname wrote:
>Problem solved by [...] running a regex to prepend a minus sign. Not
>proud of this, but it got the job done.

Just reinstate the original (correct) "E" instead!

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

Gilles Gilles
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Brian Barker
Brian Barker wrote
> Yes, if your locale needs semicolons as separators commas will not do.
> That is definitely
> what error 509 will be signalling.

Odd that the locale would change the sign used in functions.


Brian Barker wrote
> That is because you have - weirdly - chosen to mess up my formula,
> changing my "E" (for east) into "O" (for west). So *of course* the
> longitude signs are now reversed. Reinstate my "E" and everything will
> work.

Because in the document O = West, not East (which I forgot to point out).

https://postimg.cc/QH0q5qmn <https://postimg.cc/QH0q5qmn>  

Thank you.



--
Sent from: http://document-foundation-mail-archive.969070.n3.nabble.com/Users-f1639498.html

--
To unsubscribe e-mail to: [hidden email]
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

Brian Barker Brian Barker
Reply | Threaded
Open this post in threaded view
|

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

At 10:02 22/11/2020 -0700, Gilles Noname wrote:
>Brian Barker wrote
>>Yes, if your locale needs semicolons as separators commas will not
>>do. That is definitely  what error 509 will be signalling.
>
>Odd that the locale would change the sign used in functions.

Odd, maybe, but true. Sorry that my formulae did not exactly match your needs.

>Brian Barker wrote
>>That is because you have - weirdly - chosen to mess up my formula,
>>changing my "E" (for east) into "O" (for west). So *of course* the
>>longitude signs are now reversed. Reinstate my "E" and everything will work.
>
>Because in the document O = West, not East (which I forgot to point out).

I know that, but it seems you don't! You didn't need to point it out:
I had realised - and explained so in my original message. But you are
still missing the point here. You thought you needed to translate my
formula, but you didn't: I wrote exactly what you needed here. My
test mentions "E" for east (or "est") but properly distinguishes
between "E and "O". By changing "E" to "O" you have not translated
into French but changed east to west. I'm sure east in France is the
same east as anywhere else, and similarly west - no matter that the
names are different! (Or do French compasses point southwards?!) You
need "E" for French "est" in my formula just as you would need "E"
for English "east". Surely you can see that swapping east and west
(as you chose to do) will swap the signs of your longitude values and
introduce the error?

>https://postimg.cc/QH0q5qmn

Yes: I based my formulae on that original picture! Note that includes
"E" for east/est.

I trust this helps.

Brian Barker


--
To unsubscribe e-mail to: [hidden email]
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

Michael D. Setzer II Michael D. Setzer II
Reply | Threaded
Open this post in threaded view
|

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

In reply to this post by Gilles
=Wanted to see if could make a time formula that worked without have to reenter the data in
column a to a time format. Uses regex to convert to time format. First set uses original
combined value. Second set uses values split in B and C to make D and E simpler.
Just interesting. Know I sometimes have data that is created by other systems, and having
to manually reenter data might be an issue?


Another option without having to reformat the original text in column A
assuming that it is create by something else in that format.
Re-entering all the data to time format might be an issue.


Time format is definitely the simplist option.


With Original type data in A41 and A420315'090E 4312'814N
0005'12O 4259'12N</div>
In Cells D41 to E42 Get the Values
D41: 3.2750;
E41: 43.4261111111111
D42: -0.086666666666667
E42:42.9866666666667


Formula D41: Uses REGEX to convert data to time format value, and if ends with other
than E negates it.
=VALUE(REGEX(REGEX(LEFT(A41,FIND(,A41)-1),['],:,g),[&quot;NSEWO],,g)*24)*(IF(MID(A41,FIND(&quot;,A41)-1,1)=E,1,-1))


Forumula E41:
=VALUE(REGEX(REGEX(MID(A41,FIND(,A41)+1,20),['],:,g),;[NSEWO],,g)*24)*(IF(RIGHT(A41,1)=N,1,-1))


Same formula for D42 and E42


In A45 and A46 put same values as input.In Columns B and C split the values upFormula B45
=LEFT(A45,FIND(,A45)-1)Formula B46
=LEFT(A46,FIND(,A46)-1)Formula C45
=MID(A45,FIND(,A45)+1,20)Formula C46
=MID(A46,FIND(,A46)+1,20)
Formula in D45
=VALUE(REGEX(REGEX(B45,['],:uot;,g),[NSEWO],,guot;)*24)*(IF(RIGHT(B45,1)=E,1,-1))
Formula in E45
=VALUE(REGEX(REGEX(C45,['],:uot;,g),[NSEWO],,guot;)*24)*(IF(RIGHT(C45,1)=N,1,-1))




--
To unsubscribe e-mail to: [hidden email]
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

Next » 12