How to Extract Text from Excel Descriptions Based on Keywords in WPS Spreadsheets
Question details
The user needs a formula to extract text from product descriptions in an Excel-compatible spreadsheet when specific keywords determine the returned fit value.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has product descriptions and needs to populate a Fit column based on whether specific keywords appear.
- Observed behavior
- The goal is to automatically return the matching fit value when a description contains one of the specified keywords.
Prepare a list of keywords and the exact value each keyword should return, and decide whether keyword matching should be case-insensitive.
Use SEARCH, ISNUMBER, and IFS to Return Values for Multiple Keywords
Use this when you have several keywords and need a clean, expandable formula in WPS Spreadsheets.
The SEARCH function finds a text string inside another cell and returns the starting position, while ISNUMBER changes that result into TRUE when a match exists. Wrapping these tests in IFS lets you evaluate multiple keywords and return the first matching value.
Click the first cell in the Fit column where you want the keyword-based result to appear.
Type =IFERROR(IFS(ISNUMBER(SEARCH("Keyword1",A2)),"Result1",ISNUMBER(SEARCH("Keyword2",A2)),"Result2"),"Default") and press Enter. Replace A2, Keyword1, Keyword2, Result1, Result2, and Default with your actual data.
Drag the fill handle from the bottom-right corner of the formula cell down through the remaining product description rows.
Use SWITCH with TRUE and SEARCH for Exact Keyword-Result Pairs
Use this when you prefer a single SWITCH formula and have a fixed list of keyword-result pairs.
Create Keyword-Based Text Extraction Formulas in WPS Spreadsheets
WPS Spreadsheets supports the same SEARCH, ISNUMBER, IF, IFS, and SWITCH functions you need, so you can build keyword-based extraction formulas and keep your workbook fully compatible.
- 1. Open your workbook: Launch WPS Spreadsheets and open the Excel workbook that contains the product descriptions and Fit column.
- 2. Select the Fit cell: Click the cell where you want the keyword-based result, such as B2.
- 3. Enter the keyword formula: Type your SEARCH/ISNUMBER/IFS or SWITCH formula, for example =IFERROR(IFS(ISNUMBER(SEARCH("slim",A2)),"Slim Fit",ISNUMBER(SEARCH("regular",A2)),"Regular Fit"),"Other").
- 4. Fill the column: Drag the fill handle down to apply the formula to all product description rows and review the returned Fit values.
Frequently Asked Questions
Which WPS Spreadsheets function can check whether a description contains a keyword?
Use SEARCH inside ISNUMBER. For example, =ISNUMBER(SEARCH("slim",A2)) returns TRUE if cell A2 contains "slim".
How can I return a different value for each keyword?
Wrap multiple ISNUMBER(SEARCH(...)) tests in IFS or SWITCH. Each test returns the matching value in the order you specify, and you can add a default value when no keyword matches.
Why does my formula return #VALUE! when the keyword is not found?
SEARCH returns a #VALUE! error when the text is not found. Use ISNUMBER around SEARCH or wrap the entire formula in IFERROR to provide a default result instead of an error.
Is keyword matching case-sensitive in WPS Spreadsheets?
The SEARCH function is not case-sensitive. If you need case-sensitive matching, use FIND instead of SEARCH in the same formula structure.
Can I save this keyword formula file in Excel format?
Yes. WPS Spreadsheets can save workbooks as Microsoft Excel .xlsx, and the SEARCH, ISNUMBER, IFS, and SWITCH functions remain compatible.
