How to split a multi-line cell into separate cells

How to split a multi-line cell into separate cells?

There are at least two known solutions to this problem: splitting multiple lines within a cell to separate cells, each containing one line of the original, multiple-line cell.

1.    Text-to-Columns
This method uses the delimiter CTRL+J.
Typing these two keys together, creates the linefeed (hidden) character.

 

2.    Power Query

In Power Query, you choose:
Split Column->By Delimiter->split using special characters->

Insert special character->linefeed

 

But what I want to show you today is a method I've invented, which is (I believe) both the fastest and the simplest.
The solution can "materialize" thanks to the new TEXTSPLIT function in Excel 365 (I'm an "Office Insider" user but I believe that this function is already available to all 365 users, and if not, it will soon be).

So, without further ado let's dive into my solution.

1.    Our  multi-line cell looks like this (Cell A1):

     PIC 1: Cell A1 is multi-line and we want to split each line into a separate cell

 

 

2.    We define the delimiter. In cell I1 we type: =CHAR(10)

CHAR(10) is the ASCII linefeed character.

 

                                       PIC 2: defining the delimiter

 

3.    In cell B4, we type the following formula:

=TEXTSPLIT(A1, I1)

And the contents of cell A1 (5 lines) are split into 5 cells: B4-F4.

 

                                       PIC 3: splitting horizontally

 

And of course, we can split vertically as well:

 

                   PIC 4: splitting vertically

 

That's all, folks.

Hope you liked it.