Excel Text to Columns: Split Cell Text at Line Breaks

How-to basics · Updated November 21, 2026

To split cell text wherever it contains an in-cell return, use Data > Text to Columns, choose Delimited, select Other, then press Ctrl+J in the delimiter box. The box may look empty because Ctrl+J inserts the line-feed character used for a line break; click Finish to place each part in a separate column.

What a return means in an Excel cell

A return in this context is a line break inside a cell, not the Enter key used to move between cells. People often create one by pressing Alt+Enter while editing a cell in Excel for Windows. For example, a single cell might contain a person’s name, street address, and city on three lines. The cell still occupies one worksheet cell, even though its contents appear on multiple lines.

Excel stores the common in-cell line break as a line-feed character, represented in formulas by CHAR(10). The Text to Columns wizard does not offer a visible “line break” delimiter option, which is why Ctrl+J is useful: it enters that character into the custom delimiter box. It is not the same as typing the characters C, t, r, and l, or a caret notation such as ^J.

This method is useful for text pasted from forms, reports, email, or other spreadsheets when each line represents a separate field. It is most predictable when the lines have the same meaning and order in every record.

Split line breaks with Text to Columns

In Excel for Microsoft 365 on Windows, work through the wizard as follows:

  1. Select the cells in the single column that contain the line breaks. If the source is in A2:A20, select that range rather than selecting the entire worksheet.
  2. Choose Data > Text to Columns on the ribbon.
  3. In the Convert Text to Columns Wizard, select Delimited, then choose Next.
  4. Under Delimiters, select Other. Click in the small box beside it and press Ctrl+J. The box may remain visually blank; the preview is the useful check.
  5. Review the preview. If the breaks are recognized, each part should appear in its own prospective column. Choose Next if you need to adjust column formats or the destination, then select Finish.

The source cells are divided across columns on the same rows. For instance, a cell containing “Mira Chen”, a line break, “14 Cedar Road”, another line break, and “Portland” becomes three cells in that row: Mira Chen, 14 Cedar Road, and Portland. The original text is replaced by the first result unless you set a different destination in the final wizard step.

Check the preview and choose a destination

The preview helps confirm that Excel recognizes the character you entered. If it still shows the whole entry as one field, return to the delimiter step and make sure the cursor was in the Other box when you pressed Ctrl+J. The character is invisible, so an apparently empty box does not by itself mean the delimiter was missed.

Before finishing, check the cells to the right of the source column. Text to Columns writes the split values into adjacent columns; it does not insert new worksheet columns to make room. If those cells already contain information, the results could overwrite it. Copy the source data to a spare area or choose an empty destination in the wizard when you need to preserve the original.

In the final wizard step, Excel also lets you choose a column format, such as General or Text, and set a destination. Text can be a safer choice for identifiers that have leading zeros, such as 00427, because General may interpret values as numbers and remove those zeros. For names and addresses, General is usually adequate, but review dates and numeric-looking strings before accepting the result.

What happens with blank lines or uneven records

Every recognized line break acts as a separator. If a cell has three lines, the output can occupy three columns; if another cell has only two, its third output position may be empty. This matters when the rows represent structured records. A missing middle line can shift the meaning of later fields: a city might land under the address column if the first line is blank.

The wizard includes a Treat consecutive delimiters as one option. When selected, adjacent delimiters are treated as a single separator, so repeated line breaks do not necessarily preserve an empty field between them. Leave it unchecked when blank lines represent meaningful empty positions and you want to retain them. Use the preview and a few representative records to confirm the result before processing a large range.

Text to Columns splits every matching delimiter; it does not know which line is a name, address, or note. If line counts vary or empty lines carry meaning, splitting automatically may need a follow-up cleanup. A consistent layout is a data-quality issue as much as a delimiter setting.

When a formula is a better option

Text to Columns is a one-time conversion: it replaces or relocates values, and later edits to the source do not automatically update the split results. A formula is more suitable when the source may change and you want the split output to recalculate. In Microsoft 365 or Excel 2024, TEXTSPLIT can divide a value using the line-feed character.

With the multiline text in A2, enter =TEXTSPLIT(A2,CHAR(10)) in an empty cell. If A2 contains Mira Chen, 14 Cedar Road, and Portland on separate lines, the formula spills those three items horizontally into neighboring cells. Keep the spill area empty or Excel may show a #SPILL! error. Unlike Text to Columns, the formula result is linked to A2 and recalculates when A2 changes.

If the source came from an external system and contains carriage returns as well as line feeds, a stray nonprinting character can remain after splitting. With the same text in A2, use =TEXTSPLIT(SUBSTITUTE(A2,CHAR(13),""),CHAR(10)) to remove carriage-return characters before splitting at line feeds. If the split still looks wrong, inspect a sample and confirm the actual line-break characters rather than applying cleanup blindly.

To put each line vertically in separate rows instead, use =TEXTSPLIT(A2,,CHAR(10)) with the text in A2. The omitted second argument leaves the column delimiter unused; the third argument supplies the row delimiter. TEXTSPLIT is available in Microsoft 365 and Excel 2024, but not in every older Excel release. In an older version, Text to Columns is a practical built-in alternative when columns are the desired layout.

Line breaks versus other delimiters

A line break is only one possible separator. Text to Columns can also split on a tab, comma, semicolon, space, or a custom character. Choose the actual separator in the source rather than the one that looks most familiar. For example, a comma-separated list that also contains commas inside quoted addresses may need more care than a simple line break between fields.

For a repeated task involving many files, Power Query may be a better fit than manually opening the wizard each time. It is designed to import and transform data through repeatable steps, while Text to Columns is convenient for a quick worksheet conversion. The choice depends on whether the task is a one-off cleanup or part of a recurring data-import process.

On Excel for Mac, the desktop ribbon also provides the Text to Columns command under Data, though keyboard behavior and dialog details can vary by version and keyboard layout. The method relies on entering a line-feed delimiter; if Ctrl+J does not work in the Mac dialog, use a formula or check the help for the installed Excel version. Excel for the web may present a different interface; if the wizard is unavailable, open the workbook in desktop Excel or use TEXTSPLIT where supported.

Common problems and practical checks

  • Nothing splits: Confirm the Other delimiter box had focus when you pressed Ctrl+J. The character will not appear as ordinary text, so use the preview to verify it.
  • Results overwrite nearby values: Undo immediately if possible, or restore a saved copy. Before finishing, ensure the destination columns are empty or choose another destination.
  • Leading zeros disappear: Set the affected output column to Text in the wizard, or check the result before using it as an identifier.
  • Some rows split differently: Check for missing lines, extra line breaks, or inconsistent field order. The wizard applies the same delimiter rule to every selected cell.
  • Unexpected characters remain: The source may contain carriage returns or other nonprinting characters. Test cleanup on a copy and inspect a small sample first.

For a safer workflow, duplicate the source column before conversion, process a few representative rows, and compare the outputs with the original. Once the results are correct, you can keep the original as an audit trail or remove it if it is no longer needed.

Frequently Asked Questions

Can Excel Text to Columns split at an Alt+Enter line break?
Yes. In the wizard’s Delimited step, select Other and press Ctrl+J in its delimiter box. The line break itself is not visible there, so verify that the preview separates the lines.

Does Ctrl+J mean I should type the letters Ctrl+J?
No. Click the Other delimiter box and press the Ctrl and J keys together. Excel inserts the line-feed character, which may make the box look blank.

Will Text to Columns put each line on a new worksheet row?
No. It normally places each part in a separate column on the original row. To split into rows, use a row-oriented formula such as TEXTSPLIT with a row delimiter in a supported Excel version, or use a different transformation workflow.

Can I keep the original multiline text?
Yes. Copy the source column before running the wizard, or set a different destination in the wizard’s final step. Do not rely on adjacent cells being safe if they already contain data.

Why does my formula show #SPILL!?
TEXTSPLIT returns results into neighboring cells. Clear the cells where those results need to appear, including cells that look blank but contain content, and check that the formula is not inside an Excel Table that blocks the spill range.

Related

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.