Excel Split Cell by Line Break: Formulas and Steps Explained

Formulas & functions · Updated November 21, 2026

To split a cell at each line break in Excel for Microsoft 365 on Windows, enter =TEXTSPLIT(A2,CHAR(10)) in an empty cell, replacing A2 with the cell you want to divide. The results spill into neighboring columns; use =TEXTSPLIT(A2,,CHAR(10)) instead to place the line-separated parts in rows.

What splitting a cell by a line break means

A line break is a character inside a cell that moves text onto a new line without starting a new cell. People often add one while typing by pressing Alt+Enter in Excel for Windows. For example, a cell might contain a person’s name, department, and location on three separate lines. The line breaks are stored as characters within that cell, even though the text appears on multiple lines.

Excel does not divide one cell into smaller cells. Instead, a formula or data tool takes the text between line breaks and places each piece in a separate cell. That distinction matters: a split can overwrite neighboring data if you use a method that writes results into existing cells, and a formula’s results need room to spill.

Use TEXTSPLIT for a quick, repeatable result

TEXTSPLIT is usually the easiest choice when you want a formula that updates if the original text changes. It is available in Microsoft 365 and Excel 2024. Enter the formula in a blank cell next to the source, then press Enter. Excel places each section on a separate column automatically.

  • With the text in A2 and a line break between each part, enter =TEXTSPLIT(A2,CHAR(10)). For the example text North, Sales, and London on separate lines, the result is North in the first cell, Sales in the next, and London in the third.
  • With the same text in A2, enter =TEXTSPLIT(A2,,CHAR(10)) to put North, Sales, and London in separate rows in the same column.

CHAR(10) represents the line-feed character commonly used for a line break in an Excel cell. In the formula, it acts as the delimiter: Excel looks for that character and separates the text at each occurrence. The first formula supplies the delimiter as the column delimiter. The second leaves that argument empty and supplies CHAR(10) as the row delimiter.

Spilled results occupy a rectangular range, so make sure the cells to the right or below the formula are empty, depending on the direction you chose. If they are not, Excel displays a #SPILL! error rather than replacing their contents. Remove or move the blocking values, then the result can appear.

Prepare the source data before splitting

First check that the cell really contains line breaks. Select it and look at the formula bar, or turn on Home > Wrap Text to see the lines more clearly in the worksheet. A cell can look as if it has multiple lines because the column is narrow and text wraps automatically; that visual wrapping is not a line-break character and will not split with CHAR(10).

For text entered directly in Excel, Alt+Enter on Windows is a common way to insert a line break while editing a cell. On a Mac, the shortcut is Control+Option+Return in Excel. Data copied from websites, email, or other systems may use different line-ending characters, so imported text can require cleanup before splitting.

Before changing a large range, try the formula on one representative cell. Check whether every row has the same number of line-separated parts, whether any lines are blank, and whether the data to the right or below is empty. If the text is important, keep the original column until you have checked the split results.

Handle imported line endings and blank lines

Some imported text includes a carriage return as well as a line feed. If splitting on CHAR(10) leaves an unexpected character at the end of a result, remove carriage returns first. With the imported text in A2, use =TEXTSPLIT(SUBSTITUTE(A2,CHAR(13),""),CHAR(10)). This removes CHAR(13) before TEXTSPLIT separates the remaining text at CHAR(10).

Consecutive line breaks create empty parts. For example, if a cell contains North, a blank line, then London, the ordinary split can return an empty result between the two words. If you want to skip empty sections, the optional ignore_empty argument can be set to TRUE. With the source text in A2, use =TEXTSPLIT(A2,CHAR(10),,TRUE). This can be useful for messy imports, but it also removes intentional blank lines, so check whether those gaps carry meaning.

TEXTSPLIT can also return results in both directions when you provide both a column delimiter and a row delimiter. For a basic line-break split, choose one direction at a time. That makes the output easier to review and avoids creating a two-dimensional result unexpectedly.

Split with Text to Columns for a one-time conversion

If you want fixed values rather than a formula linked to the original, use Text to Columns. This is useful for a one-off cleanup, but it writes the separated pieces into cells and does not automatically update if the source changes.

  1. Make sure the cells to the right of the source column are empty, and consider copying the original data to a backup column.
  2. Select the cells you want to split, then choose Data > Text to Columns.
  3. Choose Delimited, then select Next. Choose Other as the delimiter and press Ctrl+J in the delimiter box to enter a line feed. The box may look blank because the delimiter is a nonprinting character.
  4. Select Finish and inspect the resulting columns, especially cells that had fewer or more lines than their neighbors.

Text to Columns is designed to split into columns, not to stack the parts into rows. It also has no live connection to the original cell once the split is complete. The line-feed entry can be awkward to see, so test it on a copied sample first. If the delimiter is not recognized in your Excel build or environment, TEXTSPLIT is a clearer option when available.

Choose the right output direction and method

The best approach depends on whether you need a temporary view, permanent values, or a repeatable transformation. A formula is convenient for a small range or data that changes. Text to Columns is straightforward for a one-time split into columns. For recurring imports or larger data-cleaning workflows, Power Query can make the transformation part of a refreshable process.

  • Choose columns when each line represents a field, such as name, department, and location, and you want one record per row.
  • Choose rows when each line is an item that should become its own record, such as a list of tags or products.
  • Choose a formula when you want results to recalculate from a source cell. Remember that dynamic-array formulas need clear spill space.
  • Choose Text to Columns when you need a one-time result and want to convert the split into ordinary cell values.

Power Query is useful when you repeatedly import the same kind of file. In Excel for Windows, select the data and choose Data > From Table/Range to open the Power Query Editor. Select the relevant column and use Split Column > By Delimiter. The exact way to specify a line-feed delimiter can depend on the source and editor interface; verify it with a small sample before applying the transformation to a full dataset. When the query is loaded, refreshing it can repeat the transformation on updated source data.

Common problems and how to fix them

  • The formula returns the whole text unchanged: the cell may use a different line-ending character, or the apparent lines may only be visual wrapping. Check the source and try removing CHAR(13) as shown above.
  • You see #SPILL!: one or more cells in the output area contain data, merged cells, or another obstruction. Clear the intended spill range or move the formula.
  • The split creates empty cells: the original has consecutive line breaks or a break at the beginning or end. Decide whether blanks are meaningful; use the ignore_empty option only if you intend to discard them.
  • Text to Columns replaces existing content: the output cells were not empty. Undo immediately if appropriate, or restore the backup and repeat with enough blank columns available.
  • Some records produce more pieces than others: the results may occupy different widths. Review outliers before using the split data in a report, lookup, or import.

One common misconception is that Wrap Text creates the same kind of line break as Alt+Enter. It does not. Wrap Text moves text to another displayed line to fit the cell width, while a manual or imported line break is an actual character in the cell. Changing the column width can alter wrapping, but it will not change where a formula using CHAR(10) splits the text.

Frequently Asked Questions

Can I split one Excel cell into rows instead of columns?
Yes. With the source text in A2, enter =TEXTSPLIT(A2,,CHAR(10)) in an empty cell. Each line spills into a separate row below the formula.

Does TEXTSPLIT work in every version of Excel?
No. TEXTSPLIT is available in Microsoft 365 and Excel 2024. In versions without it, try Data > Text to Columns for a one-time split into columns, or use Power Query where available.

How do I split a cell that contains both commas and line breaks?
You can use TEXTSPLIT with a comma as the column delimiter and CHAR(10) as the row delimiter, but that creates a grid and may not match the layout you want. If you only need to split at line breaks, use CHAR(10) alone and handle commas separately.

Why does my formula not split text pasted from another app?
The pasted content may use carriage returns, another separator, or only visual wrapping. Try the cleanup formula that removes CHAR(13), and confirm the cell actually contains a line-feed character before relying on the result.

Can I split multiple cells at once?
For a range, you can copy a formula down beside each source cell, adjusting the reference for each row. If you use a dynamic-array formula, give each result enough empty space; overlapping spill areas will cause errors.

Check the results before using them

After splitting, compare a few outputs with their original cells. Confirm that names, numbers, punctuation, and blank lines landed where intended. If you plan to replace formulas with fixed values, copy the results and use Paste Special > Values; keep the original data until the converted range has been checked. This final review helps catch inconsistent line breaks and prevents a tidy-looking split from silently changing the meaning of a record.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

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

Spotted an error in this guide? Let us know.