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 |
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 |
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 |
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 |
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 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) > > 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 |
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 |
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 |
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 |
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 |
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 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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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),["NSEWO],,g)*24)*(IF(MID(A41,FIND(",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 |
Free forum by Nabble | Edit this page |