How to find 4th space from right

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!

edit retag close merge delete

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.

( 2018-03-08 21:51:52 +0200 )edit

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 ;-)

( 2018-03-08 22:14:51 +0200 )edit

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?

( 2018-03-08 23:32:32 +0200 )edit

Sort by » oldest newest most voted

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. more Comments 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. ( 2018-03-08 22:15:17 +0200 )edit 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! ( 2018-03-08 22:18:05 +0200 )edit If this answers your question, please check the checkmark icon to the left of the Answer, and karma-permitting upvote it. ( 2018-03-08 22:31:31 +0200 )edit 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. ( 2018-03-08 23:26:02 +0200 )edit 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)

( 2018-03-08 23:45:00 +0200 )edit