How to Search for Text in Excel and Return Different Values
Question details
The user wants to evaluate the text within a cell for specific keywords (like "apple" or "orange") and output different numeric values or a blank depending on which keyword is found.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Categorizing or assigning numeric values to cells automatically based on the presence of predefined text strings embedded within longer text.
- Observed behavior
- A combined nested formula is needed to evaluate multiple conditions and output the corresponding value (1, 2, or blank) without triggering value errors.
Ensure the text you are searching through does not contain unintended partial matches, as the SEARCH function will identify substrings within longer words (for example, 'pineapple' will trigger a match for 'apple').
Use Nested IF, ISNUMBER, and SEARCH Functions
Combine the IF, ISNUMBER, and SEARCH functions to dynamically check for multiple text strings and return specific assigned values based on what is found.
The SEARCH function finds a text string inside another and returns its numerical starting position. Since SEARCH is not case-sensitive, it's ideal for general text scanning. By wrapping it in the ISNUMBER function, we convert the positional number (or error if not found) into a simple TRUE/FALSE condition. We can then use nested IF statements to evaluate these conditions one by one.
Click on the empty cell where you want the resulting value to be displayed.
Type the formula: =IF(ISNUMBER(SEARCH("apple",C3)),1,IF(ISNUMBER(SEARCH("orange",C3)),2,"")) into the formula bar. Replace "apple" and "orange" with your specific search terms, and change "C3" to the reference of the cell you are evaluating.
Press Enter. The cell will now display 1 if 'apple' is found, 2 if 'orange' is found, or remain blank if neither word is present.
Click the cell with the completed formula, grab the small square fill handle in the bottom-right corner, and drag it down to apply the logic to the rest of your dataset.
Easily Process Complex Data Formulas in WPS Office
WPS Spreadsheet fully supports advanced nested formulas like IF, ISNUMBER, and SEARCH, allowing you to categorize data, extract text, and automate your workflows seamlessly.
- 1. Open your dataset in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the document containing the text you need to search.
- 2. Input the nested formula: Click the cell where you want the result, and type your IF, ISNUMBER, and SEARCH combination directly into the formula bar.
- 3. Apply and drag: Press Enter to execute the function, then use the fill handle to quickly populate the rest of your data column.
Frequently Asked Questions
Is the SEARCH function case-sensitive?
No, the SEARCH function is not case-sensitive. If you need a strict case-sensitive text search (e.g., distinguishing 'Apple' from 'apple'), you should use the FIND function instead of SEARCH in your formula.
What happens if neither word is found in the cell?
In the provided nested IF formula, the final argument is an empty string (""). If neither target word is found, the cell will simply appear blank. You can change the "" to "Not Found" or 0 if you prefer a visible output.
How can I add a third word to search for?
You can add more conditions by nesting another IF statement. For example, insert IF(ISNUMBER(SEARCH("banana",C3)),3,"") into the false-value position of the last IF statement to assign the value 3 for 'banana'.
Will this formula match partial words?
Yes, SEARCH looks for substrings. Searching for 'apple' will successfully return a match even if the cell contains words like 'applesauce' or 'pineapple'.
