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
<[email protected]>
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
<[email protected]>
> 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 <[email protected]
> >:
>
> > 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 <
> > [email protected]>:
> >
> >> Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg <
> >> [email protected]>:
> >>
> >>> 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 <[email protected]>:
> >>>
> >>>> 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: [email protected]
> >>>> 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: [email protected]
> 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:[email protected]
mailto:[email protected]
Guam - Where America's Day Begins
G4L Disk Imaging Project maintainer
http://sourceforge.net/projects/g4l/
+------------------------------------------------------------+
--
To unsubscribe e-mail to: [email protected]
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
