Ask Your Question

multiple cell lines to columns [closed]

asked 2014-02-09 23:02:42 +0100

PeterSkotte gravatar image

updated 2015-09-12 21:59:50 +0100

Alex Kemp gravatar image

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? :D

Thanks, Peter

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 09:35:30.471290


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?

m.a.riosv gravatar imagem.a.riosv ( 2014-02-09 23:34:21 +0100 )edit

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?


PeterSkotte gravatar imagePeterSkotte ( 2014-02-10 00:06:39 +0100 )edit

2 Answers

Sort by » oldest newest most voted

answered 2014-02-10 02:14:09 +0100

m.a.riosv gravatar image

updated 2014-02-10 02:19:56 +0100

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


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

edit flag offensive delete link more


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 gravatar imageoweng ( 2014-02-11 04:03:25 +0100 )edit

answered 2014-02-10 00:28:28 +0100

oweng gravatar image

updated 2014-02-10 00:40:01 +0100

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:


A sed expression like this[1]:

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

... will produce:


... 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).

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-02-09 23:02:42 +0100

Seen: 2,091 times

Last updated: Feb 10 '14