Split multiple row data in one cell into separate cells

Excel Tip #008

Split multiple row data in one cell into separate cells

To force the contents of a cell onto multiple lines in the same cell, you can insert a line break within the cell by holding down the ALT key and pressing Enter.

But what if you have a worksheet with many of these cells with multiple lines and you would like to put the contents of each row in separate cells. Doing this manually by copying and pasting parts of each cell would be very slow and tedious.

So here is an option to split multiple row data in one cell into separate cells. To do this, we are going to take help from Word. This is an effective approach to break each row within a cell into separate rows in the same column.

Consider the below example where cells A1 through A4 are filled with random names where each cell occupying 5 names.

Multiple data in a single row in Excel

Copy and paste the column of data into a blank Microsoft Word document.

Copy pasted data from Excel to Word

Then copy and paste it back to Excel and each line in each cell will be split into separate cells in the same column.

Data pasted back to Excel - Transformed data