how to split multiple lines in a cell into a separate cells/columns
In Excel, you can use the Text to Columns functionality to split the content of a cell into multiple cells.
You can specify the delimiter (such as a space, comma, or tab) and the Text to Columns functionality would use this delimiter to split the content of the cells.
Examples of this include splitting first and last names, or username and domain name in email ids.
However, if you have a dataset where the delimiter is a line break (in the same cell), it gets tricky to split these multiple lines in the same cell into separate cells/columns.
In this video, I will show you how to make line break a delimiter in the Text to Columns functionality so that you can use line break to split the contents of a cell.
Here are the steps:
-- Select the entire dataset that you want to split.
-- Go to the Data tab.Data
-- In the Data Tools group, click on the Text to Columns option.
-- In the Text to Columns dialog box, in Step 1 of 3, select Delimited and click ‘Next’.
-- In Step 2 of 3, uncheck any existing Delimiters selection, and select the ‘Other’ option. Now Use the keyboard shortcut Control + J (hold the ‘Control’ key and then press the ‘J’ key). You will not see anything in the box except a tiny blinking dot (if you look hard). Also, you will see the expected result in the ‘Data preview’ section (as shown below). Click on Next.
-- In Step 3 of 3, change the ‘Destination’ cell to the one where you want the output.
-- Click on Finish.