Hi,
In Calc I have a column of data that looks like this"
Niemandsland N51° 54.948 E9° 08.886
Super Challenge - Six-Pack State Challenge Cache N38° 46.937 W121° 16.293
Ludvikova kruhova cihelna N50° 13.363 E16° 00.324
The last numbers of each row form GPS coordinates and I would like to extract them to sperate columns. The length is not fixed. The only thing I know for sure is that it starts after the 4th space from the right. How would I go about that?
Thanks for guiding me!
By the way: Would you mind to explain the strange format like N51° 54.948 E9° 08.886 and its meaning? I don’t understand it.
Yes, those are geographical (gps) coordinates. I happen to have a list of coordinates in which the name and numbers are merged together. I am trying to “unmerge” them 
That seemed clear to me. What I don’t understnad is a construct like N51° 54.948. Why 51 degrees if then comes 54.something? The coordinates I work with when using a route-planner or a navigation device don’t look this way, and the devices neither give nor understand such items. Was it 51° 54.948 angular minutes?
Yes, maybe my navi also has such an option. But why use it?
Assuming you enabled
> Tools > Options > LibreOffice Calc > Calculate > Enabl regular expressions in formulae
and you don’t shy back from a rather “complicated” RegEx, you can use
=SEARCH(" " & "[^ ]+ [^ ]+ [^ ]+ [^ ]*$";A2) to find the fourthlast space in the string from cell A2, and then the MID() function to extract the tail.
If you are not just fond of complications, I would suggest you consider to look for the pattern “N or S, then at least one digit and finally the ° character” which is described ba the slightly simpler and clearer RegEx in
=SEARCH("(N|S)[0-9]+°";A2)
See also this attached demo.
Thanks for the quick answer! Yes, I also realised that another given fact is the position of the last N in the string, and worked that out as well. But as you already noticed, coordinates can start with an S as well, very sharp! So I will give your first solution a try as well! I found an option in my (Dutch) settings that enables the English function names as well, that makes life much easier! Thanks again.
Gave it a go and replaced the MID with a RIGHT command with the same 4 spaces calculation. Seems to do better in sitautions where double spaces are used. After cleaning the input with a TRIM it looks like all is 100%. Thanks again!
If this answers your question, please check the checkmark icon to the left of the Answer, and karma-permitting upvote it.
Quoting @DeltaCon: “I found an option in my (Dutch) settings that enables the English function names as well, that makes life much easier!”
Yes. I would like to advise everybody to enable that option. Even better: Use an English UI. You may have a different locale and whatever document languages nonetheless.
For English UI: Please set the semicolon under ... Formuls > Separators > Function to avoid the comma in this role and to ease international cooperation.
Quoting @DeltaCon: “…the position of the last N in the string.”
I don’t search the last N (or S) in the string, but an
N or S followed by at least one digit and an adjacent °.
Looking for the last N (or S) may also be an option: =SEARCH("(N|S)[^NS]+$";A2)