Excel Text to Columns: Split Cells at Line Breaks Easily

To split a cell at each line break in Excel for Microsoft 365 on Windows, select the cells, choose Data > Text to Columns, select Delimited, then check Other and press Ctrl+J in its delimiter box. The box may look empty; Ctrl+J enters the line-feed character Excel uses for an in-cell new line. Finish the wizard to place the pieces in separate columns.

What a line-break delimiter does

A line break is a character inside a cell, not the same thing as moving to the next worksheet row. For example, a cell might contain three lines—North, South, and West—even though all three words are stored in one cell. When Text to Columns uses the line break as its delimiter, it treats each break as a boundary and separates the text horizontally: North in one column, South in the next, and West in the next.

In Excel, a line break inserted while editing a cell is generally a line-feed character. On Windows, you can insert one manually with Alt+Enter. Ctrl+J is the keyboard input used to specify that line-feed character as a custom delimiter in the Text to Columns wizard. It is normal for the delimiter field to show no visible symbol after you press it.

This method is useful for cleaning pasted addresses, product details, notes, or lists that were formatted on several lines inside each cell. It does not turn each line into a separate worksheet row. If rows are the desired result, use a different approach described below.

Steps to split cells with Text to Columns

Make a copy of the source data or check that the columns to its right are empty. Text to Columns can write split results into those neighboring columns.
Select the cells containing the line breaks. For a single cell, select that cell; for a list, select the relevant range in one column.
On the ribbon, choose Data > Text to Columns. In the Convert Text to Columns Wizard, choose Delimited, then select Next.
Clear any delimiter choices you do not want, such as Tab, Comma, or Space. Check Other, click its small input box, and press Ctrl+J. The box can remain visually blank.
Check the data preview. If it shows the intended divisions, select Next and choose a destination if you do not want the results to start in the original column.
Select Finish. Review the resulting columns, especially where some source cells have more line breaks than others.

For example, if A2 contains the three lines North, South, and West, and the destination is D2, the result is North in D2, South in E2, and West in F2. The original cell is not preserved as a complete, unchanged copy when the output starts in its own column, so keep a backup if you need the source intact.

Choose a safe destination and check the preview

The destination setting matters because splitting creates additional columns. If the source is in A2 and the default destination is A2, Excel writes the first piece to A2 and later pieces to B2, C2, and so on. Existing content in those neighboring cells may be overwritten. Before running the wizard, look across the full range that the output could occupy, not just at the cells directly beside the source.

To keep the original values, select an empty area as the destination in the wizard, or copy the source column to a new worksheet first. If the selected range has many rows, the longest cell determines how many output columns may be needed. A short entry may occupy only the first one or two columns while a longer entry fills several. The preview is a useful check, but it may not reveal every issue in a large, uneven dataset.

Text to Columns also offers data-format choices in the wizard. For ordinary names or notes, the default General format is usually suitable. If the pieces include codes with leading zeroes, such as 0047, choose the relevant output column in the wizard and set its column data format to Text if preserving those zeroes is important. Otherwise Excel may interpret a piece as a number and remove leading zeroes.

Empty lines, repeated breaks, and uneven data

Not every cell has the same number of line breaks. A two-line address and a four-line address will produce different numbers of filled columns, so the output can look irregular. That is expected: Excel places each piece in the next available column on that row and leaves unused cells blank.

Pay attention to empty lines. Two line breaks in succession mean there is an empty piece between them. A line break at the beginning or end of a cell can also create an empty first or last piece. In the wizard, the option Treat consecutive delimiters as one changes how consecutive delimiters are handled. If you need empty pieces to remain meaningful—for example, because each position represents a specific field—leave that option unchecked and inspect the preview. If empty lines are accidental and should not create empty output positions, checking it may be appropriate.

Also consider whether the selected cells contain other kinds of separators. Do not leave Comma, Space, or Tab selected unless those characters should split the text too. A space delimiter, for example, would split a line such as New York into two pieces in addition to splitting at line breaks. Use the preview to catch this before finishing.

When the desired result is separate rows

Text to Columns is designed to divide text across columns. It will not stack each line from one cell into a new worksheet row. If you need a vertical list—for instance, to turn a multiline task list into one task per row—use a formula that splits on a row delimiter, or reshape the data with Power Query. The best choice depends on whether you need a live result that updates when the source changes or a one-time transformation.

With the original multiline text in A2, enter this formula in an empty cell to return the pieces vertically:

=TEXTSPLIT(A2,,CHAR(10))

TEXTSPLIT uses its row-delimiter argument here; the blank argument leaves the column delimiter unspecified, and CHAR(10) represents a line feed. The formula spills its results into cells below, so those cells must be empty. This function is available in Microsoft 365 and Excel 2024; it is not available in every older Excel release. If the source contains a blank line, the split can include a blank result, so check whether that matches the intended structure.

If you need a permanent list rather than a formula result, copy the spilled values and use Paste Special > Values. If the source changes regularly, keeping the formula may be more useful because the output can recalculate. Do not place the formula where its spill range would run into existing data.

Other ways to handle line breaks

Text to Columns is convenient for a quick, one-time split across columns. Other methods may fit better when the data is large, refreshed repeatedly, or needs to be reshaped into rows.

Find and Replace: You can replace line breaks with a visible separator, such as a pipe character, and then split on that separator. In Excel’s Find and Replace dialog, Ctrl+J can enter a line break in the Find what box as well. Choose a replacement character that does not already occur in the data, or the later split may break valid text.
Power Query: For a repeatable import and cleanup process, load the data into Power Query and split the relevant column by a delimiter. The exact available choices depend on the selected split option and Excel version. Power Query is useful when you want to refresh the transformation, but it takes more setup than the wizard for a small one-off task.
TEXTSPLIT: A formula is useful when you want results linked to the source. Its delimiter arguments let you direct the split across columns or rows, but the function requires Microsoft 365 or Excel 2024, and the destination spill area must be clear.

Replacing line breaks with spaces is another option if the goal is to make each cell a single readable line rather than create separate fields. That is a different transformation: it removes the boundaries between items, so it is unsuitable when the separate lines need to remain distinct.

Windows, Mac, and Excel for the web

The steps above are for Excel for Microsoft 365 on Windows. In the Windows wizard, Ctrl+J is the key detail for entering a line-feed delimiter. The usual Windows shortcut for inserting a manual line break while editing a cell is Alt+Enter.

Excel for Mac also has a Text to Columns feature, but keyboard input and shortcut behavior can differ by keyboard layout and macOS version. If Ctrl+J does not enter the delimiter in the Mac wizard, do not assume a blank box means the character was entered. A formula using TEXTSPLIT and CHAR(10), when available in that Excel version, is an alternative; verify the result before replacing the source. On a Mac, the shortcut for inserting a manual line break in a cell is generally Control+Option+Return, rather than Windows Alt+Enter.

Excel for the web has a different interface and feature availability can change. If the Text to Columns command or a particular wizard option is not available in your browser version, open the workbook in the desktop app for the steps above. A supported TEXTSPLIT formula may also work in the web app, but check that the function is available in the account and workbook you are using.

Frequently Asked Questions

Can I use a line break directly as the Text to Columns delimiter?
Yes. In the Windows Text to Columns wizard, choose Delimited, check Other, and press Ctrl+J in the delimiter box. It is a line-feed character, so the field may look empty even when it has been entered.

Why does Ctrl+J appear to do nothing?
Ctrl+J inserts a nonprinting line-feed character, which usually has no visible mark in the box. Confirm that Other is checked and inspect the data preview. If the preview does not divide the text as expected, try the operation on a copied sample or use a TEXTSPLIT formula where supported.

Will Text to Columns put each line on a new row?
No. It puts the pieces in separate columns on the same worksheet row. To return the pieces vertically, use a row-delimiter formula such as TEXTSPLIT in a supported version, or use a data-transformation workflow that reshapes the results.

Why did my split overwrite other cells?
The wizard writes results into the destination range, which may extend to the right of the selected cells. Choose an empty destination in the wizard or move a copy of the source data to a clear area before splitting.

How do I preserve blank lines between items?
Leave Treat consecutive delimiters as one unchecked if consecutive line breaks should produce empty pieces. Then review the preview and output: leading, trailing, or repeated breaks can create blank results. If blank lines are not meaningful, removing them may be preferable.

Does this work for line breaks pasted from another program?
Often, but pasted text can contain different line-ending characters or inconsistent formatting. Test a few representative cells first. If Ctrl+J does not split a pasted sample, normalize the imported text or use a formula or Power Query approach suited to the actual characters in the data.

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.