How to Split Multiline Cell Text Using TEXTSPLIT in Excel
Question details
The user needs to separate text that contains multiple lines within a single cell into individual cells, as the standard Text to Columns wizard fails to recognize the line breaks properly.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reformatting and extracting data from a single cell containing text separated by line breaks (Alt+Enter) into multiple distinct columns or rows.
- Observed behavior
- The standard Text to Columns tool does not correctly recognize line breaks after the first line, preventing the text from being split accurately into multiple cells.
Ensure your version of Excel or Spreadsheet software supports dynamic array functions like TEXTSPLIT (available in Microsoft 365 and newer versions of WPS Office). Identify whether your text was separated by standard line breaks or carriage returns.
Split Multiline Text Horizontally (Into Columns)
Use the TEXTSPLIT function with a column delimiter to distribute the multiline text across adjacent columns.
In Excel, a standard line break created by pressing Alt+Enter is represented by the character code 10. You can use CHAR(10) as the column delimiter in the TEXTSPLIT function to tell Excel to start a new column wherever it encounters a line break.
Click on a blank cell adjacent to the cell containing your multiline text (for example, cell B1 if your data is in A1).
Type the formula =TEXTSPLIT(A1, CHAR(10)) into the formula bar.
Press Enter. The multiline text will automatically spill horizontally into adjacent columns based on the line breaks.
Split Multiline Text Vertically (Into Rows)
Modify the TEXTSPLIT function parameters to use a row delimiter, which lists the split text vertically down a single column.
Handle Carriage-Return and Line-Feed Combinations
Combine CHAR(13) and CHAR(10) as the delimiter if standard line breaks do not split the text correctly.
Effortlessly Manage Array Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array functions like TEXTSPLIT, enabling you to manage and format complex multiline text smoothly. It provides a familiar interface and comprehensive functionality to handle all your data processing needs.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the spreadsheet containing your multiline data.
- 2. Input the TEXTSPLIT formula: Select an empty cell and enter the formula =TEXTSPLIT(A1, CHAR(10)).
- 3. Press Enter to split the text: Hit Enter, and the WPS Spreadsheet engine will automatically spill your multiline text into beautifully separated cells.
Frequently Asked Questions
Why does the TEXTSPLIT function result in a #NAME? error?
The #NAME? error usually means your current version of the spreadsheet software does not support the TEXTSPLIT function. This function is available in newer versions like Microsoft 365 or the latest WPS Office. If you see this error, you may need to update your software.
Can I split multiline text without using formulas?
Yes. You can use the 'Text to Columns' feature located under the Data tab. In the wizard, choose 'Delimited', check the 'Other' box, and press Ctrl+J to insert a line break as the delimiter. However, TEXTSPLIT is highly recommended as it updates dynamically if the source data changes.
How do I combine split cells back into a single cell with multiple lines?
You can use the TEXTJOIN function to reverse the process. Use the formula =TEXTJOIN(CHAR(10), TRUE, B1:D1) to combine the cells back together. Ensure you apply the 'Wrap Text' format to the result cell so the line breaks display properly.
