How to Split Newline-Separated Text into Columns in Excel
Question details
The user needs to separate multiple items stored within a single Excel cell (separated by line breaks) into distinct columns for easier data analysis.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Formatting and parsing raw data where multi-line text entries in single cells need to be distributed across adjacent columns.
- Observed behavior
- Multiple data points are trapped within a single cell, joined by invisible line-feed characters, preventing standard spreadsheet calculations and filtering.
Ensure your dataset is formatted as an Excel Table before importing it into Power Query, and verify that there are enough empty columns to the right of your data if you choose the Text to Columns method.
Use Power Query to Split by Line Feed Delimiter
This is the most robust and recommended method for splitting multi-line cells, as Power Query handles line feeds natively and updates automatically if source data changes.
Power Query features specialized delimiter options specifically designed to handle hidden characters like carriage returns and line feeds.
When using this method, the original dataset remains untouched, and the cleaned data is generated in a new worksheet.
Select any cell in your data table, go to the Data tab on the Excel ribbon, and click 'From Table/Range' to open the Power Query Editor.
Click the header of the column containing the newline-separated text to select it. Navigate to the Home tab, click 'Split Column', and choose 'By Delimiter'.
In the Split Column dialog box, check the 'Select or enter delimiter' dropdown. If 'Line Feed' is not an option, select 'Custom' and manually type '#(lf)', or click 'Advanced options' to select the special Line Feed character.
Ensure it is set to split into 'Columns', click OK to apply the split, and then click 'Close & Load' in the Home tab to export the separated columns back into Excel.
Use Find and Replace with Text to Columns
A quick, native Excel workaround for users who do not want to use Power Query. It replaces the hidden line breaks with a standard character to enable the Text to Columns feature.
Split Text into Columns Easily with WPS Spreadsheet
WPS Spreadsheet provides powerful data cleaning tools, including advanced Find and Replace and a highly intuitive Text to Columns feature, making it incredibly easy to parse multi-line cells without needing complex queries.
- 1. Open Your Data in WPS: Launch WPS Spreadsheet and open your workbook containing the multi-line cells.
- 2. Replace Line Breaks: Press Ctrl+H. In the 'Find what' field, press Ctrl+J to input the newline character, and in 'Replace with', enter a common delimiter like a comma.
- 3. Execute Text to Columns: Highlight your column, navigate to the Data tab, and click 'Text to Columns'.
- 4. Finish the Split: Select 'Delimited', pick the comma as your delimiter, and click Finish to instantly separate your data.
Frequently Asked Questions
What is the shortcut to type a line break in Excel's Find and Replace box?
The shortcut is Ctrl+J. When you press this combination in the 'Find what' field, it inserts an invisible carriage return/line feed character, allowing you to search for line breaks created by pressing Alt+Enter.
What is the Power Query code for a newline or line feed?
In Power Query, the special character code used to represent a line feed is #(lf). Carriage returns are represented by #(cr).
Why is my Text to Columns overwriting the data next to it?
Text to Columns distributes the split data into the adjacent columns to the right. If those columns already contain data, Excel will overwrite it. Always ensure you insert enough blank columns next to your target column before performing the split.
Can I split cells by newline using a formula?
Yes. If you have a newer version of Excel, you can use the TEXTSPLIT function. The formula =TEXTSPLIT(A1, CHAR(10)) will split the contents of cell A1 horizontally into separate columns wherever a line break (CHAR(10)) occurs.
