ThisCalculator

Everyday

Excel Split Cell by Carriage Return: 3 Ways

Updated · Standard formula, shown below

Excel Split Cell by Carriage Return: 3 Ways

To split a cell by carriage return in Excel, use the Text to Columns feature with a custom delimiter, or use a formula with the newline character (CHAR(10)). This article explains both methods, plus how to handle multiple lines in a single cell and avoid common pitfalls.

What Is a Carriage Return in Excel?

A carriage return in Excel is a line break within a cell, created by pressing Alt+Enter (Windows) or Control+Option+Return (Mac). It allows you to display multiple lines of text in one cell. In formulas, this character is represented by CHAR(10). When you copy data from another source (like a website or a text file), line breaks may also appear as carriage returns, making the cell look like it contains a single long string but actually holding several lines.

Method 1: Text to Columns (Quickest for One-Time Split)

This built-in tool splits cell content based on a delimiter. Here’s how to use it for a carriage return:

  1. Select the column that contains the cells with line breaks.
  2. Go to the Data tab and click Text to Columns.
  3. Choose Delimited and click Next.
  4. In the delimiters list, uncheck all options, then check Other and press Ctrl+J in the box. (This inserts a line break character, which appears as a tiny dot or nothing.)
  5. Click Finish. The cell’s content will be split into multiple columns, one for each line.

This method overwrites the original data. If you need to keep the original, copy the column to a new location first.

Method 2: Using Formulas (Dynamic and Repeatable)

If you prefer a formula-based approach (for example, if the data updates), you can use TEXTSPLIT (Excel 365) or a combination of TRIM, MID, and SUBSTITUTE for older versions.

Excel 365: In a cell, enter =TEXTSPLIT(A1,CHAR(10)) and press Enter. The result spills into adjacent cells, each containing one line from the original cell.

Older Excel (2016, 2019): Use this array formula (confirm with Ctrl+Shift+Enter):

=TRIM(MID(SUBSTITUTE(A1,CHAR(10),REPT(" ",100)),(ROW(INDIRECT("1:"&LEN(A1)-LEN(SUBSTITUTE(A1,CHAR(10),""))+1))-1)*100+1,100))

This formula calculates the number of line breaks, then extracts each segment. It’s more complex but works without the new function.

Method 3: Power Query (For Large Datasets)

Power Query is ideal when you need to split many rows or repeat the process on refreshed data. Steps:

  1. Select your data range and go to Data > From Table/Range (create a table if prompted).
  2. In the Power Query editor, select the column with line breaks.
  3. Go to Home > Split Column > By Delimiter.
  4. Choose Custom and enter #(lf) (this represents a line feed).
  5. Click OK, then Close & Load.

Power Query splits the data into new columns and loads them back into a new worksheet. It’s non-destructive and easy to refresh.

Common Problems and Solutions

  • Text to Columns doesn’t split: The cell might contain a line feed (CHAR(10)) but also a carriage return (CHAR(13)). Try using Ctrl+J in the delimiter box, but if that fails, use Power Query with #(lf) or #(cr).
  • Trailing spaces after split: Use TRIM in formulas, or in Text to Columns, check the “Treat consecutive delimiters as one� option.
  • Split into rows instead of columns: If you need each line in a separate row, you can transpose the result or use a formula like =TRANSPOSE(TEXTSPLIT(A1,CHAR(10))).
  • Line breaks not recognized: Some systems use CHAR(13) alone (old Mac). Replace CHAR(10) with CHAR(13) in formulas, or in Power Query use #(cr).

How to Choose the Right Method

  • One-time split on a few cells: Text to Columns is fastest.
  • Dynamic data that updates: Use TEXTSPLIT in Excel 365.
  • Large datasets or recurring imports: Power Query is the most robust.
  • Need to keep original data: Copy to a new column first, or use formulas/Power Query.

Frequently Asked Questions

Can I split by a line break without losing data? Yes, copy the column to a new location first, or use formulas/Power Query which leave the original intact.

What if my cell has both commas and line breaks? Split by line break first, then apply Text to Columns with comma delimiter on the resulting columns.

How do I split into multiple rows instead of columns? Use =TRANSPOSE(TEXTSPLIT(A1,CHAR(10))) in Excel 365, or copy the split columns and use Paste Special > Transpose.

Why does my formula return a #VALUE! error? This often happens if the cell contains a line break but also other invisible characters. Use CLEAN() to remove non-printable characters, or check if the line break is actually CHAR(13).

Can I split a cell by a newline in Google Sheets? Yes, use =SPLIT(A1, CHAR(10)) or the Split text to columns feature with a custom separator.

Is there a way to split by carriage return in Excel for Mac? Yes, the same methods work. Text to Columns uses Control+Option+Return to insert the delimiter, and formulas use CHAR(10).

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.