To split text at line breaks in Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, select Delimited, then select Other and press Ctrl+J in its box. The delimiter is an invisible line-feed character; choose Finish to put each line into a separate column.
What Text to Columns Does with Line Breaks
Text to Columns divides text in each selected cell wherever it finds a delimiter you specify. A line break inside a cell is a character in the cell’s text, not a visible symbol, so Excel can use it as a delimiter just as it can use a comma, tab, or semicolon. In typical Excel cells, an inserted line break is the line-feed character, represented by CHAR(10) in a formula.
This is useful when one cell contains multiple pieces of information on separate lines, such as a person’s name and address, a list of products, or several notes pasted from another application. Text to Columns places those pieces side by side in separate columns. It does not automatically put each line on a new worksheet row.
Excel can show line breaks when Wrap Text is enabled, but wrapping alone is not a delimiter operation. A long sentence may wrap visually because the column is narrow even though the cell contains no line-break character. Text to Columns will split only at an actual character in the cell, not at a visual wrap created by the column width.
Steps to Split Cells Using Ctrl+J
- Select the cells that contain the line breaks. If you select a whole column, check that there is enough empty space to the right for the split results.
- On the ribbon, choose Data > Text to Columns.
- In the Convert Text to Columns Wizard, choose Delimited, then select Next.
- Clear any delimiter boxes that are selected, such as Tab or Comma. Select Other, click its small input box, and press Ctrl+J.
- Look at the data preview. The line-feed character does not appear as a readable mark in the box, but the preview should show the text divided at the line breaks.
- Select Next if you want to set column data formats or a destination; otherwise choose Finish.
When the wizard asks whether it should replace existing data, do not proceed if the split columns would overwrite information you need. Text to Columns writes results into cells to the right of the original values by default. To preserve the source, set a different destination in the wizard or copy the source column to a blank area first.
A Worked Example
Suppose A2 contains three lines: North, Central, and South, with each word on its own line. Selecting A2 and using Ctrl+J as the delimiter puts North in A2, Central in B2, and South in C2. If A3 contains only two lines, the first two result columns are filled for that row and the third may remain blank. Each original row is processed separately; the entries do not get gathered into one continuous list.
This horizontal layout is often right for address fields or a fixed number of attributes. For example, a cell with a street, city, and postal code on separate lines can become three columns. It is less suitable when cells contain different numbers of list items and the goal is one item per worksheet row. In that case, use a formula that spills vertically or another transformation method instead.
Prepare the Data and Protect Existing Values
Before splitting a large range, make a copy of the source data or use a blank worksheet. The wizard changes the selected cells and may replace neighboring content. A quick check of the destination area prevents a common mistake: starting with the right delimiter but losing data already present in the cells where Excel writes the results.
- Check a few sample cells to confirm that the breaks are real and consistent.
- Make sure the output area has enough columns for the cell with the most lines.
- Decide whether the original text should be kept, copied elsewhere, or replaced.
- Review the preview and, after finishing, inspect several rows for missing or misplaced pieces.
Text to Columns is best when the same kind of split should be applied to a selected range and the result can occupy columns. It is not a complete data-cleaning tool: it will not recognize that one line is a name and another is an address, or infer a different structure for every record. If line breaks appear inconsistently, split a small sample first and decide how to handle the exceptions.
When a Formula Is a Better Choice
A formula can be preferable when you want to retain the original cell, update the results when the source changes, or split a single cell without opening the wizard. In Microsoft 365 and Excel 2024, TEXTSPLIT can use the line-feed character as its delimiter. It returns a dynamic array, so neighboring cells must be empty for the results to spill.
With the line-separated text in A2, enter this formula in B2 to spill the pieces horizontally into B2, C2, and subsequent cells:
=TEXTSPLIT(A2,CHAR(10))
For example, if A2 contains North, Central, and South on separate lines, the formula returns those three values across the row. To spill the same entries vertically instead, with the source still in A2, enter this formula in B2:
=TEXTSPLIT(A2,,CHAR(10))
The empty second argument leaves the column delimiter unspecified; CHAR(10) is supplied as the row delimiter. The result spills down from B2. TEXTSPLIT is available in Microsoft 365 and Excel 2024; it is not a solution for older Excel versions that do not include the function. If Excel reports a spill error, check for existing values in the cells where the output needs to appear.
Line Breaks, Carriage Returns, and Imported Text
Line breaks may arrive from pasted text, exported files, web pages, or other programs. Some sources use a line feed, while others may include a carriage return as well. Excel’s in-cell line breaks are commonly line feeds, but imported text can contain different or mixed characters. If Ctrl+J does not split the content as expected, inspect a sample with a formula or test the data in a separate column before processing the full range.
For text where carriage returns may be attached to line feeds, Microsoft 365 or Excel 2024 can remove the carriage returns before splitting. With the original text in A2, enter this in B2 to split the cleaned text horizontally:
=TEXTSPLIT(SUBSTITUTE(A2,CHAR(13),""),CHAR(10))
SUBSTITUTE removes CHAR(13), and TEXTSPLIT then divides the remaining text at CHAR(10). The formula assumes line feeds mark the intended boundaries. If the source uses a different separator, this formula will not identify it automatically; use the actual separator or clean the source first. Keep the original column until you have checked that the pieces are complete and in the expected order.
Common Problems and How to Fix Them
- The preview does not split: Confirm that Other is selected and Ctrl+J was entered while the cursor was in its input box. The character is invisible, so rely on the preview rather than expecting to see a symbol.
- Only some cells split: The cells may contain different types of breaks, or some may only look like they have separate lines because of text wrapping. Check the underlying contents and test a representative cell.
- Text is overwritten: The destination cells to the right were not empty. Undo immediately with Ctrl+Z if appropriate, or restore the saved copy, then rerun the operation with a safe destination.
- Pieces appear in the wrong direction: Text to Columns produces columns. Use the vertical TEXTSPLIT version if one item per row is required and your Excel version supports it.
- Extra blank results appear: Repeated line breaks can represent empty lines, and a line break at the beginning or end can create an empty piece. Decide whether those blanks are meaningful before removing them.
Ctrl+Z on Windows undoes a recent Text to Columns operation in many ordinary cases, but do not rely on undo as your only safeguard for valuable data. Save a copy or use a separate destination before making broad changes.
Windows, Mac, and Excel for the Web
The ribbon route in desktop Excel for Windows is Data > Text to Columns, and Ctrl+J is the commonly used way to enter the line-feed delimiter in the Other box. Excel for Mac has a similar Text to Columns command, though ribbon arrangement and keyboard behavior can differ by release. If the shortcut does not enter the delimiter on your Mac, use the formula approach where TEXTSPLIT is available, or consult the command options in that version rather than guessing at a visible delimiter.
Excel for the web may not expose the same wizard controls in every current interface. If you cannot find Text to Columns or cannot enter the invisible delimiter, open the workbook in the desktop app. The TEXTSPLIT formulas work in supported Microsoft 365 versions, including the web app, when the function is available. On Windows, Alt+Enter inserts a line break while editing a cell; on Mac the line-break shortcut differs by keyboard and Excel release, so use the app’s editing shortcut reference if creating sample data.
Frequently Asked Questions
What is the line-break delimiter in Excel Text to Columns?
Use Ctrl+J in the Other delimiter box. It inserts the line-feed character, which is normally the character used for line breaks inside Excel cells.
Why can’t I see Ctrl+J in the delimiter box?
The line-feed character is not displayed as ordinary text, so the box can look blank. Check the data preview to see whether Excel is splitting the cell at the intended places.
Does Text to Columns split line breaks into rows?
No. Text to Columns places the split pieces in separate columns on the same worksheet row. Use the vertical TEXTSPLIT formula when you need a supported formula-based result down a column.
Can I split line breaks without changing the original cell?
Yes. Use TEXTSPLIT in Microsoft 365 or Excel 2024, or set a different destination in the wizard. A formula also updates its result when the source text changes.
Why does my cell look like it has line breaks when it does not split?
Text may wrap automatically because the column is narrow. Widen the column or inspect the cell contents; Text to Columns splits actual delimiter characters, not visual line wrapping.
Can I use this method for multiple selected cells?
Yes. Select the range before opening Text to Columns, and Excel applies the delimiter to each selected cell. Ensure the output columns are clear and check rows that contain fewer or more lines than others.
Related
- Excel Table Data Sorting: Steps, Options, and Fixes
- Excel Spreadsheet Not Responding? Causes and Fixes
- Excel Suddenly Not Responding? Causes and Fixes
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Data Validation and Quality Assurance in ExcelIn Excel, data validation limits what people can enter in a cell, while quality assurance (QA) is the…
- SharePoint Excel: How to Group Dates in a PivotTableTo group SharePoint data by date in an Excel PivotTable, open the workbook in Excel for Microsoft 365…
- Excel Data Table Reference Format: A Practical GuideIn Excel, a table reference uses the table name and, when needed, a column or item in square…
- How to Delete a Full Page in Word: Step-by-StepTo delete a full page in Word, click anywhere on that page, open Go To, enter \page, and…
- How to Delete Grouping in Excel: Rows, Columns & SheetsTo delete a row or column grouping in Excel, select the grouped rows or columns, then choose Data…
- Excel AutoSave: How to Turn It Off and Keep Changes SafeTo turn off AutoSave for the workbook you have open in Excel for Microsoft 365 on Windows, switch…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.