How to Split Multiline Cell Contents Using Formulas in Excel
Question details
The user needs to automatically split text containing line breaks within a single cell into multiple adjacent cells, ensuring dynamic updates when the source cell changes.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Migrating data from Google Sheets or handling multiline text entries where grouped values need to be separated into distinct columns automatically.
- Observed behavior
- Text values are grouped in a single cell separated by line breaks; the goal is to dynamically distribute them across multiple cells rather than splitting them manually.
Ensure you are using a version of Microsoft 365 or a modern version of Excel that supports dynamic array functions like TEXTSPLIT. For older versions, alternative methods like the Text to Columns wizard are required.
Use the TEXTSPLIT Formula in Modern Excel
The most efficient and dynamic way to split multiline text in Microsoft 365 or modern Excel versions.
The TEXTSPLIT function is a modern Excel feature that acts like the Text to Columns wizard but works as a dynamic formula. It allows your split data to update automatically whenever the original cell is modified.
Click on an empty cell where you want the split data to begin (for example, cell C2).
Type =IFERROR(TEXTSPLIT(B2,CHAR(10)),"") into the formula bar. In this formula, B2 is the cell containing your multiline text, and CHAR(10) represents the line break character.
Press Enter. The formula will automatically split the contents of the source cell at every line break and spill the results into the adjacent columns.
Use the Text to Columns Feature for Older Excel Versions
A manual alternative for older Excel versions that do not support dynamic array formulas like TEXTSPLIT.
Split Text and Manage Data Easily with WPS Office
WPS Spreadsheet provides powerful data processing tools, including advanced formulas and intuitive Text to Columns features, allowing you to split multiline text effortlessly while saving time.
- 1. Open your file: Launch WPS Office and open your spreadsheet document containing the multiline text.
- 2. Select the target cell: Click on the cell with line-separated data that you wish to split into multiple cells.
- 3. Access Data tools: Navigate to the Data tab on the top ribbon and select 'Text to Columns'.
- 4. Apply line break delimiter: Choose the 'Other' delimiter option, press Ctrl+J to input a line break character, and finish the process to separate your data accurately.
Frequently Asked Questions
What does CHAR(10) mean in the Excel formula?
CHAR(10) represents a line break character (carriage return) in Excel formulas. Using it as a delimiter in the TEXTSPLIT function tells Excel to separate the text wherever a new line occurs within the cell.
Why am I getting a #NAME? error when using TEXTSPLIT?
The #NAME? error typically occurs if your version of Excel does not support the TEXTSPLIT function. This function is primarily available in Microsoft 365 subscriptions and Excel for the Web. If you are using a standalone version like Excel 2019 or 2016, you will need to use the Text to Columns feature instead.
How do I split multiline text into rows instead of columns?
To split the text into rows vertically instead of columns, you can modify the formula to use the row delimiter argument in TEXTSPLIT. Leave the column delimiter argument empty and use the row delimiter like this: =TEXTSPLIT(B2,,CHAR(10)).
