![[TEST] Document Foundation Community](https://communitytest.documentfoundation.org/images/discourse-logo-sketch.png){kind=logo}
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
Can Calc do this, or should I look elsewhere?
Thank you.
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.
Date sent: Sat, 21 Nov 2020 22:11:59 -0700 (MST)
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
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
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:12I 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,086666666666667You 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. 
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
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
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"?
In French : N nord = north, S sud = south, O ouest = west, E est = east
Philip
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:
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):
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...
<gurus.knugum@gmail.com>
Date sent: Sun, 22 Nov 2020 12:43:35 +0100
coords from DMS to
decimals?
<users@global.libreoffice.org>
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:
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.
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.
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
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
Just reinstate the original (correct) "E" instead!
I trust this helps.
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).
Thank you.
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?
Yes: I based my formulae on that original picture! Note that includes "E" for east/est.
I trust this helps.
Brian Barker
=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))