Extract the Two Words After a Specific Word in Excel with WPS Office
Question details
The user needs an Excel-compatible formula that returns only the two words immediately after a specified keyword, even when the source text uses mixed uppercase and lowercase letters.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is working with text strings in a spreadsheet and wants to isolate the two words that follow a designated word in each cell.
- Observed behavior
- The TEXTAFTER function alone returns the entire remainder of the cell after the keyword, not only the next two words, and case may vary.
Confirm that your version of WPS Office Spreadsheets supports TEXTAFTER, TEXTSPLIT, and TAKE, and identify the column containing your text strings. For mixed-case matching, use TEXTAFTER's match_mode argument or wrap the source text with LOWER.
Use TEXTAFTER, TEXTSPLIT, and TAKE with Case-Insensitive Matching
Best for modern WPS Office and Excel versions because it directly returns only the first two words after the delimiter and ignores letter case.
The formula first uses TEXTAFTER to remove everything up to and including the keyword. TEXTSPLIT then separates the remaining text into individual words, and TAKE keeps only the first two. TEXTJOIN joins them back into a single result.
Launch WPS Office Spreadsheets and open the workbook that contains the text strings, or create a new sheet and enter sample text in column A.
Click an empty cell where you want the extracted two words to appear, such as B3.
Type =LET(text,TEXTAFTER(A3,"message",1,1),words,TEXTSPLIT(TRIM(text)," "),TEXTJOIN(" ",TRUE,TAKE(words,,2))) and replace A3 and "message" with your cell reference and keyword.
Press Enter to confirm the result, then drag the fill handle down to apply the formula to the remaining rows.
Extract Two Words After a Keyword in WPS Office Spreadsheets
Use TEXTAFTER, TEXTSPLIT, and TAKE directly in WPS Office Spreadsheets—no macro or manual splitting required. The formula handles mixed-case keywords and saves the workbook in Microsoft Excel-compatible .xlsx format.
- 1. Open WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open your Excel workbook, or click File > New to create a new sheet.
- 2. Enter your text and keyword: Type or import your text strings into a column such as A2:A10 and note the keyword you want to match.
- 3. Use the recommended formula: In the adjacent column, enter the formula in the first row and replace A2 and "message" with your cell reference and keyword.
- 4. Fill the formula down: Drag the fill handle down to apply the formula to the remaining rows and verify the extracted words.
- 5. Save as .xlsx: Click Menu > Save As and choose .xlsx to keep full Microsoft Excel format compatibility.
Frequently Asked Questions
Can I make the formula case-insensitive so "Message" and "MESSAGE" both match?
Yes. Use TEXTAFTER(A3,"message",1,1) to set the match_mode argument to case-insensitive. Alternatively, you can wrap the source text with LOWER and search for a lowercase keyword.
What happens if the delimiter word is not found in the cell?
TEXTAFTER returns a #N/A error. You can wrap the formula with IFERROR, for example =IFERROR(LET(text,TEXTAFTER(A3,"message",1,1),words,TEXTSPLIT(TRIM(text)," "),TEXTJOIN(" ",TRUE,TAKE(words,,2))),"Not found").
Can I extract a different number of words after the keyword?
Yes. In the TAKE function, change the second argument from 2 to the number of words you want. For example, TAKE(words,,3) returns the next three words.
Does this TEXTAFTER and TEXTSPLIT formula work the same in WPS Office Spreadsheets?
Yes. WPS Office Spreadsheets supports TEXTAFTER, TEXTSPLIT, TAKE, LET, and TEXTJOIN in modern versions. Keep WPS Office updated and use .xlsx files for full Excel formula compatibility.
