Expand Multi-Row Text in A Cell to Multiple Cells

Expand Multi-Row Text in A Cell to Multiple Cells

Problem description & analysis:

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

original table

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

desired table


Use SPL XLL to do this:

Enter fullscreen mode
Exit fullscreen mode

As shown in the picture below:

result table with code entered

news@q function generates a new sequence by computing members of an existing sequence; ~2 is the 2ndmember 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.

Discover more from Coursity

Subscribe to get the latest posts sent to your email.


No comments yet. Why don’t you start the discussion?

    Leave a Reply

    Your email address will not be published. Required fields are marked *