Multiple cell lines to columns

[PeterSkotte]

I have a range of cells with multiple lines like:

Cell 1:
Here is a line of text

Here is a line of text

Here is a line of text

Cell 2:

Here is another line of text

Here is another line of text

Here is another line of text



There are line breaks at each line.

This I would like to convert to:

Cell 1, Row 2 | 3 | 4:

Here is a line of text | Here is a line of text | Here is a line of text

Cell 2, Row 2 | 3 | 4:

Here is another line of text | Here is another line of text | Here is another line of text

I have searched hi and low for a hint on how to do that with no luck so far. Can any of you gurus help?

Thanks, Peter

[mariosv]

Hi @PeterSkotte, please can you detail what is “Cell 1, Row 2 | 3 | 4” seems the same as the source, or is it the three lines in one with a new separator?

[PeterSkotte]

In each cell there are 3 lines with line breaks. I would like to get each line into a new column for the same row they come from. So line 1 goes to column 2, line 2 goes to column 3, line 3 goes to column 4 and so on.
Hope that makes sense?

/peter

[mariosv]

Time ago I did a file with a matrix formula to split a cell in cells for a specific separator like Text to column.

Split-Cell-In-Cells_LikeTextToColumns]

maybe you need to extend or reduce the matrix to the number of columns to generate.

[oweng]

I have to mark this up for sheer determination I know this can be done, but it does lead to huge formulas. Well done.

[oweng]

There is no easy way to achieve what you require, and any solution will be highly dependent on the data. For this reason, if your spreadsheet contains other data that is not arranged in this manner I would advise copying the multi-line cell ranges to a separate spreadsheet and working on them in isolation.

One option is to use a macro, as this can be programmed to iterate over the lines in each cell, which is essentially what you require. Exactly what this macro would look like I have no idea, but it will use the same type of logic as the sed expression given below.

Another option is to export the multi-line cells to CSV and run a pattern matching expression over the data to replace the newline characters. Exporting to CSV should provide content like:

"Cell1Line1
Cell1Line2
Cell1Line3"
"Cell2Line1
Cell2Line2"
"Cell3Line1
Cell3Line2
Cell3Line3
Cell4Line4"

A sed expression like this[1]:

$ sed ':a;N;$!ba;s/\([^\"]\)\n/\1\",\"/g' multi-line_cells.csv > single_line_cells.csv

… will produce:

"Cell1Line1","Cell1Line2","Cell1Line3"
"Cell2Line1","Cell2Line2"
"Cell3Line1","Cell3Line2","Cell3Line3","Cell3Line4"

… which can be re-imported and copied as required.

[1] Refer here for details about what the loop and branch part of the expression (:a;N;$!ba;) does, and here for details about the capture group part of the expression (\([^\"]\) and \1).