Hi,
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 )
“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?)
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.
Sorry. I was distracted again. Posts crossed. As I judge my suggestions a bit more concrete, I didn’t delete.
This works super fine! Thank you!
I’ve found two exceptions though:
Could you extend this regexp to match it?
^(.*)\s+(\d+[a-zA-Z]?)(/(\d+[a-zA-Z]?))?$ → $1|$2|$4
It seems not to remove doesn’t remove “/” between numbers… Gives: “Street name|12|/23”
Did you use the replacement string ($1|$2|$4) I mentioned above, or the one from my original answer ($1|$2|$3)?
Oops haven’t noticed, sorry. Works flawlessly! Thanx alot!