Excel Formulas to Split Text by Line Break

Manually separating multi-line cell data in Excel is a tedious, error-prone chore that disrupts analytical workflows. When organizing complex reporting data from standard funding sources-such as federal grants or private capital allocations-clean formatting is essential. Fortunately, mastering modern array formulas grants users instant, automated control over cluttered datasets.

Stipulation: This dynamic method requires Excel 365 or Excel 2021, as legacy versions lack support for the necessary spilled formula engine.

By leveraging the TEXTSPLIT function paired with CHAR(10), you can instantly parse text at every line break. Below, we will detail how to implement this formula, review concrete examples of horizontal and vertical splitting, and troubleshoot common parsing errors.

Managing data in Excel often feels like digital housekeeping. One of the most common-and frustrating-scenarios occurs when you import data from CRM systems, web forms, emails, or PDFs, only to find multiple pieces of information crammed into a single cell, separated by line breaks. Whether it is a multi-line mailing address, a list of product codes, or meeting notes, analyzing this data requires you to separate these lines into individual cells.

Depending on your version of Excel, there are several ways to accomplish this. In this comprehensive guide, we will explore the best formulas and techniques to split text by line breaks, starting with the modern Excel 365 functions and moving back to robust legacy solutions and built-in features.

Understanding the Secret Character: CHAR(10)

Before writing any formulas, you need to know how Excel recognizes a line break. In Windows-based Excel, a line break (specifically a Line Feed) is represented by character code 10. In Excel formulas, we reference this character using the CHAR(10) function.

Note for Mac Users: macOS historically used character code 13 (Carriage Return) for line breaks, represented as CHAR(13). However, modern versions of Excel for Mac generally support CHAR(10). If you find your formulas are not working on a Mac, try swapping CHAR(10) with CHAR(13).

Method 1: The Modern Way – Using TEXTSPLIT (Excel 365 & 2021+)

If you are using Microsoft 365 or Excel for the Web, you have access to the ultimate text manipulation tool: the TEXTSPLIT function. This dynamic array function completely replaces older, convoluted workarounds.

1. Splitting Horizontally (Across Columns)

To split text in cell A2 across columns by its line breaks, use the following formula in cell B2:

=TEXTSPLIT(A2, CHAR(10))

How it works: Excel reads the text in A2, identifies every instance of a line break, and spills the split values horizontally into adjacent columns to the right.

2. Splitting Vertically (Down Rows)

If you prefer to list the split items down a single column rather than across multiple columns, you can bypass the column delimiter argument and use the row delimiter instead:

=TEXTSPLIT(A2, , CHAR(10))

Notice the extra comma before CHAR(10). This tells Excel to skip the column delimiter argument and split the text into rows instead.

3. Handling Empty Lines

Sometimes, multiline cells contain consecutive line breaks, resulting in blank cells when split. You can instruct TEXTSPLIT to ignore empty spaces by adding the fourth argument set to TRUE:

=TEXTSPLIT(A2, CHAR(10), , TRUE)

Method 2: The Classic Workaround (Excel 2019 and Older)

If you or your coworkers are on older versions of Excel, you won't have access to TEXTSPLIT. Instead, you must rely on a combination of TRIM, MID, SUBSTITUTE, REPT, and LEN.

While this formula looks intimidating, it is a highly reliable master formula used by Excel pros for decades. Put this formula in cell B2 and drag it to the right:

=TRIM(MID(SUBSTITUTE($A2, CHAR(10), REPT(" ", LEN($A2))), (COLUMN(A1)-1)*LEN($A2)+1, LEN($A2)))

How This Legacy Formula Works:

1. SUBSTITUTE($A2, CHAR(10), REPT(" ", LEN($A2))): This replaces every line break with a massive block of spaces. The number of spaces is equal to the total length of the original text. This pushes each word far apart from the next.
2. MID(..., (COLUMN(A1)-1)*LEN($A2)+1, LEN($A2)): The MID function acts as a precision scissors. The COLUMN(A1) part acts as an incremental counter. As you drag the formula to the right, it calculates exactly where each chunk of text starts and cuts out a segment equal to the length of the original text.
3. TRIM(...): Finally, TRIM sweeps away all the excess spaces added in step 1, leaving you with just the clean, separated text.

Method 3: The FILTERXML Alternative (Excel 2013-2019 on Windows)

For mid-era Excel versions on Windows, there is an elegant alternative to the bulky formula above. By converting your text string into a basic XML structure, you can extract items by index using FILTERXML.

To split text in A2, paste this formula into B2 and drag it to the right:

=FILTERXML("<t><s>" & SUBSTITUTE($A2, CHAR(10), "</s><s>") & "</s></t>", "//s[" & COLUMN(A1) & "]")

How it works:

• The formula wraps your text in XML tags, turning a cell like "Apple[LineBreak]Banana" into <t><s>Apple</s><s>Banana</s></t>.
• FILTERXML then reads this code and uses the XPath "//s[1]" to grab the first item, "//s[2]" for the second, and so on as you drag it across columns (thanks to the dynamic COLUMN(A1) counter).

Method 4: Quick Non-Formula Solutions

Sometimes you don't need a dynamic formula that updates automatically. If you just need a quick, one-time cleanup, Excel has two powerful built-in tools for the job.

Option A: "Text to Columns" with a Secret Shortcut

The classic "Text to Columns" tool can easily parse line breaks if you know the keyboard shortcut to enter a line break as a delimiter.

1. Select the column containing the multiline text.
2. Go to the Data tab and click Text to Columns.
3. Choose Delimited and click Next.
4. Uncheck all default delimiters (Tab, Comma, etc.).
5. Check the box for Other.
6. Click inside the "Other" input box and press Ctrl + J on your keyboard. (You won't see text appear, but you will see a tiny blinking dot or a slight visual shift-this is the hidden line break character).
7. Click Next, define your destination cell, and click Finish.

Option B: Using Power Query

For repeatable data cleansing pipelines, Power Query is the gold standard.

1. Select your dataset, go to the Data tab, and click From Sheet/Table/Range.
2. Inside the Power Query Editor, right-click the header of the column you want to split.
3. Select Split Column > By Delimiter.
4. In the dropdown, select Custom.
5. Check the box that says Split using special characters.
6. Select Insert Special Character and choose Line Feed (or Carriage Return depending on your source).
7. Choose whether to split into Columns or Rows under Advanced Options, then click OK.
8. Click Close & Load to return the clean data back to Excel.

Troubleshooting & Pro Tips

Why is my TEXTSPLIT formula displaying as a single line?

If your split formulas aren't shifting data across columns or rows but are instead bunching up, ensure that Wrap Text is toggled correctly on your home tab. Sometimes Excel maintains visual line-break formatting even after splitting unless the cell format is refreshed.

Handling carriage returns alongside line breaks

Some external systems export data containing both Carriage Returns and Line Feeds (CRLF, or CHAR(13) & CHAR(10)). If your split data contains weird spacing or unprintable characters, clean the data first by nesting a SUBSTITUTE function inside your formula:

=TEXTSPLIT(SUBSTITUTE(A2, CHAR(13), ""), CHAR(10))

This removes the carriage returns before processing the standard line feed breaks.

Summary: Which Method Should You Choose?

By mastering these techniques, you can confidently tackle messy, imported multiline data and format it into clean, usable tables in a matter of seconds.