How to Keep the First Two or Three Lines in an Excel Cell
Question details
The user wants to extract specific starting lines from a cell containing multiline text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Filtering or truncating multiline text inside a single cell to display only the first two or three lines.
- Observed behavior
- The user needs a functional formula combination to split the text by line breaks, isolate the required number of lines, and combine them back into a single cell.
Verify that you are using a modern spreadsheet version (such as Microsoft 365, Excel 2021, or WPS Office) that supports dynamic-array functions like TEXTSPLIT and TAKE.
Use Dynamic Array Functions (TEXTJOIN, TAKE, TEXTSPLIT)
Extract specific lines by splitting the cell contents at line breaks, grabbing the first few lines, and joining them back together.
This method utilizes three powerful text-manipulation formulas. TEXTSPLIT separates the multiline text into an array based on the line break character (CHAR(10)). TAKE grabs a specified number of items from that array, and TEXTJOIN merges those items back together with line breaks.
Click on an empty cell adjacent to your multiline text, for example, cell B2 if your data is in cell A2.
Type the formula `=TEXTJOIN(CHAR(10),TRUE,TAKE(TEXTSPLIT(A2,CHAR(10)),,2))` to extract the first two lines.
If you need to keep the first three lines instead of two, change the final number in the formula from 2 to 3, making it `=TEXTJOIN(CHAR(10),TRUE,TAKE(TEXTSPLIT(A2,CHAR(10)),,3))`.
Press Enter to execute the formula. Then, select the result cell, navigate to the Home tab on the ribbon, and click 'Wrap Text' so the extracted lines display on separate lines within the cell.
Process Multiline Text Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN, TAKE, and TEXTSPLIT. You can easily manage complex text extraction tasks without needing expensive software subscriptions.
- 1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your multiline text.
- 2. Input the array formula: Select the target cell and enter `=TEXTJOIN(CHAR(10),TRUE,TAKE(TEXTSPLIT(A2,CHAR(10)),,2))`.
- 3. Format the result: Press Enter, select the cell, and click 'Wrap Text' under the Home tab to display the formatted text properly.
Frequently Asked Questions
Why does my formula return a #NAME? error?
This error usually means your version of Excel does not support dynamic array functions like TEXTSPLIT and TAKE. You must use Microsoft 365, Excel 2021 or later, or a compatible alternative like WPS Office to use these formulas.
How can I keep the last two lines instead of the first two?
You can modify the TAKE function by using a negative number. Change the last parameter to -2 like this: `=TEXTJOIN(CHAR(10),TRUE,TAKE(TEXTSPLIT(A2,CHAR(10)),,-2))` to extract the last two lines.
Why does the extracted text appear on a single line?
Even though the formula successfully added the line break characters (CHAR(10)), the cell will display the text on a single line until you enable the Wrap Text option. Select the cell, go to the Home tab, and click Wrap Text.
