Hey all,
Just trying to help my mother edit her book list she has in libre office. She added the year of the book and how many stars she gave it in the same column as the title. Now she wants them in their own columns. I know there has got to be an easy way to do this, I just don’t know how. Any help is appreciated, Thanks! 
Okay so I’ve separated it all but not so cleanly. Now all of the years are spread across 12 different columns. Is there a way to merge these? Here is what it looks like issue - Album on Imgur
Quoting @onorato: “Now all of the years are spread across 12 different columns.”
This means, I suppose, that the part in front of the year contained up to 11 spaces. You didn’t explain thoroughly enough what you actuall had.Doing data processing without sufficient information about the composition of the data is a very safe way to the hell of wrong results. Be glad to have gotten wrong results clearly calling “we are wrong”. Results being wrong in a silent way are the ultimate plague.
This depends, of course, basically on the way the “year and stars” were entered, and if there is a reliable format (syntax) your mother sticked to.
Suppose all the cell contents look like “1998 3*”. Then insert an empty column right of this one, select the column under discussion, choose the tool > ‘Data’ > ‘Text to Columns…’, choose the space as the only field delimiter, ‘OK’. Ready.
If the cells in the ctrical columns look different, tell us exactly how.
(Editing with respect to the comment below by the OQ:)
OK. As I tried to explicate above the appropriate way to do things like this depends heavily on the syntax of the entries to split (and the strictness of the obedience to it). Users not aware of the technical implications tend to use spreadsheet cells as a surrogate for scratch labels in a way more appropriate for jam glasses.
As you exemplified now the syntax may be (expressed as a RegEx) (^[^0-9]*[0-9]{4}.*$ or similar.
But your example, just one, doesn’t even contain a ‘Stars Part’, and it doesn’t at all tell much relevant for a good solution:
-1- Is there always a name in front of the year?
-2- May the name also consist of less or more words?
-3- Can there also be delimiters different from space -:,;()[] e.g?
-3- May there also occur digits in the name part like in Mary Smith (1) e.g?
-4- Additional specifics?
‘Text to Columns’ cannot delimit based on Regex. Thus you would need to use one or more helper columns and the appropriate formulas.
Based on the very incomplete information I have, I would suggest to SEARCH for the Regex [0-9]{4}($|[^[0-9]) to get the last character position +1 of the name part. The MID function can then be used for cutting out the up to three relevant parts of the original contents. Thsi still assumes you can assure a few things not expressly mentioned here.
Thanks for the response, but I was unable to do it. All of the Stars were moved manually because there were only a few dozen. The format of the cell contents now looks like this “Anita Shreve 2011”. The amount of spaces varies so when I do what was suggested above the dates go into 8 different columns. Thanks again for any insight!
Hello @onorato,
The merging of your 12 columns can be done within a minute , with a little work…
- Make a safety backup copy of your document.
- Select the first column to be merged, by clicking on the column header;
- Copy or Cut the selected column by pressing
CTRL+CorCTRL+X - Select the next column to be merged, by clicking on the column header;
- Press
CTRL+SHIFT+Vto invoke the “Paste Special” dialog; - Enter the settings in the “Paste Special” dialog as in the image below;
- Click OK ( or press ENTER ).
Repeat these steps for all 12 columns.
