ExcelInsider Team
If you have data in Excel cells separated by line breaks using Alt + Enter and want to split that data into multiple columns, you might notice that Excel’s Text to Columns tool doesn’t handle line breaks by default. Unlike commas or tabs, line breaks are special characters that need a slightly different approach.
In this article, you’ll learn the best ways to split Excel cells by line breaks using the Text to Columns tool, Power Query and an alternative formula. These techniques cover different scenarios depending on your Excel version and complexity of your data.
Steps to use Text to Columns tool in Excel:
➤ Select the cells containing the data with line breaks (e.g., A2:A6).
➤ Go to the Data tab and click Text to Columns under Data Tools.
➤ Choose Delimited and click Next.
➤ In the Delimiters section, check Other and press Ctrl + J (this inserts the line break character).
➤ You will see your Data Preview at the bottom which means Excel recognizes the line break. Then, hit Next.
➤ Choose General and click Finish.
Use Text to Columns Feature with Line Breaks as Delimiters
This method is ideal when you want a quick, one-time split of cell contents based on line breaks. The Text to Columns tool is typically used with commas or tabs, but you can force it to recognize line breaks by manually entering the line break character using Ctrl + J .
Steps:
➤ Select the cells containing the data with line breaks (e.g., A2:A6).
➤ Go to the Data tab and click Text to Columns under Data Tools.
➤ Choose Delimited and click Next.
➤ In the Delimiters section, check Other and press Ctrl + J (this inserts the line break character).
➤ You will see your Data Preview at the bottom which means Excel recognizes the line break. Then, hit Next.
➤ Choose General and click Finish.
Excel will now split the cell contents into columns wherever there was a line break. Then, you can add column names separately.
Note:
This works best if line breaks are consistent and used as your sole delimiter.
Using TEXTSPLIT Function to Convert Text to Columns with Line Break (Excel 365 / 2021)
If you’re using Excel 365 or Excel 2021 and want a solution that updates automatically when the source data changes, the TEXTSPLIT formula is perfect. It splits a cell’s content into columns at each line break and adjusts in real time when new data is added or edited.
Steps:
➤ Suppose cell A2 contains text with line breaks.
➤ In cell B2, type this formula:
=TEXTSPLIT(A2,CHAR(10))
➤ Press Enter.
This formula splits the content of A2 cell into the next columns wherever a line break occurs.
➤ Drag the formula down to apply it to other rows.
➤ Optionally, format your data according to your choice such as aligning to the center and adding column headers for split data.
Use Power Query to Split Data by Line Breaks
Power Query provides a robust way to handle line breaks and other delimiters, especially with large datasets or recurring imports. It offers a neat way to manage and transform multiline text efficiently which is fully automated.
Steps:
➤ Select your data range and go to Data >> Get & Transform group >> From Table/Range.
➤ Confirm your data range and click OK to load it into Power Query.
➤ Select the column with line breaks.
➤ On the Transform tab, click Split Column >> By Delimiter under Text Column.
➤ Choose Custom delimiter and press Ctrl + J with the pre-existing text to insert a line break.
➤ Select Each occurrence of the delimiter, then click OK.
➤ Click Close & Load under Home tab to return the split data to Excel.
Your data will now be split into columns based on line breaks. Optionally, format your data and change column titles for better presentation.
Frequently Asked Questions
Why is Ctrl + J used in the Text to Columns method?
Ctrl + J inserts a line break character (ASCII character 10, also known as CHAR(10)) into the delimiter box. Although it appears empty, Excel interprets this as a valid delimiter and uses it to split text wherever a line break occurs.
Can I use Text to Columns with line breaks in older versions of Excel?
Yes, the Text to Columns tool works in older Excel versions like 2013 or 2016. However, these versions don’t support TEXTSPLIT, so you’ll need to stick to static splitting or use Power Query as an alternative.
Why is TEXTSPLIT function splitting into rows instead of columns?
Because CHAR(10) is being used as the row delimiter instead of the column delimiter. To split into columns, the correct formula is: =TEXTSPLIT(A2,CHAR(10)). Here, placing the line break in the column delimiter argument tells Excel to split horizontally.
Wrapping Up
In this tutorial, you learned two practical ways to split Excel cell contents by line breaks using the Text to Columns tool for one-time conversions, the TEXTSPLIT formula and even Power Query for dynamic and auto-updating results. Feel free to download the practice file and share your feedback.
