RaqForum 41 No.
191 View  • 
     ...

Expand multi-row text in a cell to multiple cells

 

In the following table, column A is category and column B includes one or multiple lines of text where line break is the separator.

A

B

1

Account Number

Interaction

2

1

Jan 1,2023 - Hello.

3

2

Jan 2, 2023 - Good morning.
Jan 3, 2023 - Good night.
Jan 4, 20
Jan 5, 2023 - Good night.
Jan 6, 2023 - Good afternoon.

4

3

Jan 1,2023 - Good night.
Jan 2, 2023 - Good afternoon.

The task is to expand each multi-line cell under column B into multiple cells and copy column A value.


A

B

1

Account Number

Interaction

2

1

Jan 1,2023 - Hello.

3

2

Jan 2, 2023 - Good morning.

4

2

Jan 3, 2023 - Good night.

5

2

Jan 4, 20

6

2

Jan 5, 2023 - Good night.

7

2

Jan 6, 2023 - Good afternoon.

8

3

Jan 1,2023 - Good night.

9

3

Jan 2, 2023 - Good afternoon.

Use SPL XLL to do this:

=spl("=?.news@q(~2.import@si();[get(1)(1),~])",A2:B4)

Picture7png

news@q function generates a new sequence by computing members of an existing sequence; ~2 is the 2nd member of the current variable; import@si parses a string into a sequence of single-line strings according to the carriage return; get() function returns the loop variable according to the layer number during a multilayer loop.


https://www.reddit.com/r/excel/comments/1crmocu/how_do_i_separate_a_cell_into_different_rows/

 
 •  191 View  •