Ask Your Question
0

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

Comments

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?

/peter

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

2 Answers

Sort by » oldest newest most voted
1

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.

Split-Cell-In-Cells_LikeTextToColumns]

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

edit flag offensive delete link more

Comments

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
0

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:

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

edit flag offensive delete link more

Question Tools

1 follower

Stats

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

Seen: 1,955 times

Last updated: Feb 10 '14