Split Cells with Multiple Lines - Text-to-Columns using Carriage Return

How to split a cell by each line of data within it; this will put each line into its own separate cell. Technically, this method is Text-to-Columns using a carriage return as the delimiter.

(Carriage return is the term for the element that puts text onto a separate line within a cell in Excel.)

Sections:

Split Text based on Carriage Return

Notes

Split Text based on Carriage Return

Basically, we use Text-to-Columns with Ctrl + J as the delimiter to tell Excel to split data based on the individual lines within a cell. 

1. Select the data to split.
2. Go to the Data tab and click Text to Columns
3. Select Delimited and click Next.
   
4. Check next to Other and, in the blank box next to it, hit Ctrl + J. This is the shortcut that represents a carriage return; basically, this is the internal thing that represents a new line within a cell in Excel.
   
   Note: when you type Ctrl + J, you will not see anything appear in the box, it will remain visibly empty. However, you should see the separate lines from the cell appear separated, as in the Data preview window in the above image.
5. Click Next if you want to change where the data will go or its format; click Finish when you're done. Make sure to select a cell to the right of the current cell/column for the Destination option if you do not want to overwrite the original cell that contains all of the data in one cell.
   
6. That's it!

Notes

This is annoyingly simple and annoyingly easy to forget; almost never in Excel will you use the shortcut Ctrl + J, but that's all it takes to separate data by carriage return (new line).

To create a carriage return (new line) within a cell, hit Alt + Enter while editing a cell.

Download the sample file to see the above example in Excel.