Ask Your Question

How to find 4th space from right

asked 2018-03-08 20:46:00 +0200

DeltaCon gravatar image

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 flag offensive 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.

Lupp gravatar imageLupp ( 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 ;-)

DeltaCon gravatar imageDeltaCon ( 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?

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

1 Answer

Sort by » oldest newest most voted

answered 2018-03-08 21:29:57 +0200

Lupp gravatar image

updated 2018-03-08 21:32:12 +0200

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

See also this attached demo.

edit flag offensive delete link more


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.

DeltaCon gravatar imageDeltaCon ( 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!

DeltaCon gravatar imageDeltaCon ( 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.

librebel gravatar imagelibrebel ( 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.

Lupp gravatar imageLupp ( 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)

Lupp gravatar imageLupp ( 2018-03-08 23:45:00 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-03-08 20:46:00 +0200

Seen: 44 times

Last updated: Mar 08 '18