Excel Text to Columns Using Enter: Split Line Breaks

To split a cell at an embedded line break with Excel Text to Columns, choose Data > Text to Columns, select Delimited, check Other, and press Ctrl+J in the delimiter box. Excel uses that hidden line-feed character as the separator; pressing the Enter key by itself does not set Enter as a delimiter.

What “Enter” means in an Excel cell

People often say “Enter” when they mean one of two different things. The Enter key normally confirms an entry or moves the active cell. It is not a character stored between parts of a cell’s text, so Text to Columns cannot use a keystroke for Enter as a delimiter.

A line break inside a cell is different: it is a character within the cell’s contents. In Windows Excel, Alt+Enter inserts a line break while you are editing a cell. Text copied from another application may contain line breaks too. Excel represents a typical in-cell line break with the line-feed character, which is why Ctrl+J works in the wizard’s Other field: it enters that character, even though the field looks blank.

For example, a cell might contain three lines: “North”, “Central”, and “South”. The cell is still one cell, but its text includes two line breaks. Using Ctrl+J as the delimiter separates those three parts into neighboring columns. This method is useful for splitting multi-line addresses, notes, or lists that have been placed into a single cell.

Split line breaks with Text to Columns on Windows

Start by making sure the cells to the right of your selected data are empty, or choose a different destination in the wizard. Text to Columns writes its results into adjacent columns, so existing content there could be replaced. For a safer first attempt, copy the source column or use a blank area of the worksheet.

Select the cell or one-column range containing the line-broken text.
On the ribbon, choose Data > Text to Columns.
In the wizard, select Delimited, then select Next.
Check Other. Click its small delimiter box and press Ctrl+J. The box may appear empty; that is normal. If other delimiter boxes are checked, clear them unless you also want to split on those characters.
Check the data preview. It should show each line in a separate output column. Select Next if you need to set column formats or a destination, then select Finish.

In the final wizard step, you can specify where the split results should go. The default destination begins at the first selected cell, so the first piece replaces the original cell and the remaining pieces fill cells to its right. To retain the original text, set the destination to an empty area, such as a column farther across the sheet. If the preview does not show separate columns, do not finish yet: confirm that Other is selected and that Ctrl+J was entered in its box.

A concrete example and expected result

Suppose A2 contains “Invoice 418”, a line break, and “Due 30 Nov”. With A2 selected, the Ctrl+J delimiter should produce two fields: “Invoice 418” in the first output column and “Due 30 Nov” in the next. If the source is A2 and the default destination is used, the results appear in A2 and B2. If you set the destination to D2, they appear in D2 and E2, while A2 remains unchanged.

For a cell with three lines, the wizard creates three output fields in order. It does not automatically place each line on a new worksheet row. That distinction matters when the intended result is one item per row rather than one item per column.

Choose columns or rows before splitting

Text to Columns is designed to divide a column of text into multiple columns. It is a good fit when each original cell contains several related pieces that belong side by side, such as street, city, and postal code. If you have many source rows, Excel applies the split to each selected row and puts the corresponding pieces in the columns beside it.

If you need each line to become a separate row, Text to Columns is not the direct solution. Splitting into columns and then transposing the results can work for a small, simple range, but it can be awkward when records have different numbers of lines. Power Query may be more appropriate for repeatable transformations: load the data, split the relevant column by a line-feed delimiter, and choose the option that splits into rows. The exact controls depend on the Power Query interface and the shape of the data, so inspect the preview before loading the result.

Decide what one output record should represent before choosing a method. “One cell becomes several fields across a row” points to Text to Columns. “One line becomes one record down a list” points to a row-oriented transformation such as Power Query, or a formula that spills vertically.

Use TEXTSPLIT when you want a formula

A formula can keep the source cell intact and recalculate if its contents change. In Microsoft 365 or Excel 2024, TEXTSPLIT can divide text using a line-feed delimiter. With the original multi-line text in A2 and a clear output area starting in B2, enter =TEXTSPLIT(A2,CHAR(10)) in B2 to return the pieces across columns. For a three-line value, the results spill into B2:D2.

If each line should spill downward instead, with the same text in A2 and an empty area below B2, use =TEXTSPLIT(A2,,CHAR(10)). The second argument is left blank because there is no column delimiter; CHAR(10) is supplied as the row delimiter. A three-line value then spills into B2:B4. Keep the cells in the spill range empty, or Excel may show a #SPILL! error because it cannot place the results.

TEXTSPLIT is available in Microsoft 365 and Excel 2024, not every older Excel release. If Excel does not recognize the function, use Text to Columns or check which Excel version you have. Formulas also behave differently from a one-time split: the result is linked to the source and can update, while Text to Columns writes values into the worksheet.

Common problems and how to avoid them

The delimiter box looks blank: Ctrl+J inserts a nonprinting line-feed character, so an empty-looking box is expected. Use the preview to verify that Excel detected it.
The preview still shows one field: Re-enter Ctrl+J after clicking inside the Other box. Make sure Delimited is selected, and check that the source actually contains in-cell line breaks rather than visually wrapped text.
The text appears on several screen lines but does not split: Wrap Text can make a long value display on multiple lines without adding line-break characters. Increase the column width or inspect the cell contents; if it is only wrapping, there is no line-feed delimiter for the wizard to find.
Existing data disappears: The default destination can write over cells to the right. Undo immediately with Ctrl+Z if you have just finished and the result is wrong, or set a blank destination before running the wizard.
Leading zeros vanish or dates change: In the wizard’s final step, select the output column in the preview and choose Text when identifiers such as postal codes or account numbers must remain text. For date-like content, choose an appropriate date format only if you know how the source is arranged.
Blank lines create empty fields: A pair of consecutive line breaks represents an empty piece between them. Decide whether those blank entries matter before splitting; a formula may preserve empty pieces, while cleaning the source first may be preferable for a tidy result.

Excel for Mac and Excel for the web

Excel for Mac also includes Text to Columns, although ribbon placement and keyboard behavior can vary with the version and Mac keyboard settings. Look for Data > Text to Columns and use the wizard preview to confirm the result. Ctrl+J is the commonly used way to enter the line-feed delimiter in the Other box, but if your Mac does not enter it, use TEXTSPLIT with CHAR(10) where available, or prepare the delimiter in a compatible Windows version of Excel.

Excel for the web and desktop Excel do not always expose identical dialogs or keyboard behavior. If the Text to Columns command is unavailable in your web session, TEXTSPLIT is a practical option when the workbook has access to that Microsoft 365 function. You can also open the workbook in the desktop app for the wizard. In either environment, confirm the spill or split destination is empty and check the result before replacing the original data.

Frequently Asked Questions

Can I press Enter directly in the Text to Columns delimiter box?
No. Enter is not the character separating the text inside the cell. For in-cell line breaks, select Other and press Ctrl+J on Windows to enter the line-feed character.

Does Ctrl+J split text into rows?
Not with Text to Columns. It splits the selected text into adjacent columns. Use a row-oriented method, such as TEXTSPLIT with CHAR(10) as its row delimiter or a suitable Power Query transformation, if you need one line per row.

Why does Ctrl+J look like it did nothing?
The line-feed character is invisible in the delimiter box. The useful check is the preview: separate preview fields indicate that Excel recognized the delimiter.

Can Text to Columns split ordinary wrapped text?
No. Wrap Text changes how text is displayed, not the contents of the cell. The cell must contain actual line-break characters for Ctrl+J to split it.

Will Text to Columns keep my original cell?
Not if you accept the default destination: the first result starts in the original cell. Choose an empty destination in the wizard, or copy the source first, to preserve the unsplit text.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.