t

Step 2: Transpose each line into a new row

1. Insert a new column next to your existing data (let’s say column B).
2. Use a formula to split the text into rows:In cell B1, enter the following formula:excelCopy code=IFERROR(TRIM(MID(SUBSTITUTE($A1, CHAR(10), REPT(" ", LEN($A1))), (ROW()-ROW($B$1)+1)*LEN($A1), LEN($A1))), "")
   • Press Ctrl + Shift + Enter instead of just Enter, as this is an array formula that needs to be applied to multiple cells.
   This formula uses the CHAR(10) function to identify line breaks (new lines) within the cell text and then splits each line into a separate row.
3. Drag the formula down to apply it to all rows where you have data in column A. This will effectively split each cell containing multiple lines into separate rows in column B.

Step 3: Clean up and format

1. Remove any extra blank rows that might have been created by the formula (if applicable).
2. Copy and paste as values (optional): If you want to keep the data split into new rows permanently and remove formulas, you can copy column B, then right-click and choose ‘Paste Special’ > ‘Values’ to overwrite the formulas with the actual text.