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 <gurus.knu...@gmail.com> 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 <users@global.libreoffice.org> > 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 <gurus.knu...@gmail.com > >: > > > 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 < > > gurus.knu...@gmail.com>: > > > >> Den sön 22 nov. 2020 kl 11:22 skrev Johnny Rosenberg < > >> gurus.knu...@gmail.com>: > >> > >>> 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 <codecompl...@free.fr>: > >>> > >>>> 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: users+unsubscr...@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 > >>>> > >>> > > -- > To unsubscribe e-mail to: users+unsubscr...@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 +------------------------------------------------------------+ Michael D. Setzer II - Computer Science Instructor (Retired) mailto:mi...@guam.net mailto:msetze...@gmail.com Guam - Where America's Day Begins G4L Disk Imaging Project maintainer http://sourceforge.net/projects/g4l/ +------------------------------------------------------------+ -- To unsubscribe e-mail to: users+unsubscr...@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