Ask Your Question

Split multiple words and numbers into 3 columns

asked 2017-11-14 19:38:42 +0200

Eneen gravatar image

updated 2017-11-15 15:49:09 +0200


I've got column with such text in Calc:

Street name 10/100
Long street name 11/110
Very long street name 12/120

Is there way to make this into columns like this (without spaces before firs word and numbers):

Street name | 10 | 100
Long street name | 11 | 110
Very long street name | 12 | 120

Best regs.

(Edited a bit for better readability. @Lupp )

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2017-11-15 12:33:27 +0200

updated 2017-11-15 12:34:53 +0200

First, use Find&Replace to replace regular expression ^(.*)\s+(\d+)/(\d+)$ with $1|$2|$3 (provided that you don't have |s in your text; otherwise, replace | with some unambiguous symbol).

Then use Data-Text to Columns and choose proper symbol (|) in Separated by - Other.

edit flag offensive delete link more


Sorry. I was distracted again. Posts crossed. As I judge my suggestions a bit more concrete, I didn't delete.

Lupp gravatar imageLupp ( 2017-11-15 12:47:29 +0200 )edit

This works super fine! Thank you! I've found two exceptions though:

  • there can be letter after number like 123a or 1c in both fields
  • sometimes there is only one number without "/" like this: Very long street name 412a

Could you extend this regexp to match it?

Eneen gravatar imageEneen ( 2017-11-15 18:10:42 +0200 )edit

^(.*)\s+(\d+[a-zA-Z]?)(/(\d+[a-zA-Z]?))?$ -> $1|$2|$4

Mike Kaganski gravatar imageMike Kaganski ( 2017-11-15 19:25:39 +0200 )edit

It seems not to remove doesn't remove "/" between numbers... Gives: "Street name|12|/23"

Eneen gravatar imageEneen ( 2017-11-16 09:29:15 +0200 )edit

Did you use the replacement string ($1|$2|$4) I mentioned above, or the one from my original answer ($1|$2|$3)?

Mike Kaganski gravatar imageMike Kaganski ( 2017-11-16 09:47:14 +0200 )edit

Oops haven't noticed, sorry. Works flawlessly! Thanx alot!

Eneen gravatar imageEneen ( 2017-12-06 19:14:16 +0200 )edit

answered 2017-11-15 12:41:39 +0200

Lupp gravatar image

updated 2017-11-15 12:48:15 +0200

"Is there way ..."
This depends on the rangeof means to take account of.
Any means for tasks of the kind, however, gravely depend on a clear and complete syntax defining the range of string to which it should be applicable - and in what way exactly. The few examples you gave cannot replace explic it assurances and specifications. Trying a solution without knowing reliabla and definite specifications may just be waisting time.

Generally you will also have to decide if the task should be solved exclusively by formulas and preserving the original compound data, or conversion.
A conversion done by 'Data' > 'Text to Columns...' must be prepared. if the syntax to recognize the end of any compound is described by the RegEx [0-9]+/[0-9]+$ and no additional occurrence of / is assured e.g. you can use the 'F & R' with ([0-9]+/[0-9]+$) in 'Find:' and /$1 in 'Replace' in the first step. (Regard the spaces! 'Only current selection', 'Regular expressions' selected.)
The second step is then to make sure that enough adjacent columns are empty and to apply 'Data' > 'Text to Columns...' with / as the only delimiter.

(Just for curiosity: Are you numbers in each row actually assured to be 2 digits and twice the same??
If not - as I assume: Why did you choose such examples?)

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2017-11-14 19:38:42 +0200

Seen: 1,054 times

Last updated: Nov 15 '17