Excel Formula to Check for Multiple Text Criteria with OR
Question details
The user needs to create an Excel formula that evaluates a cell containing mixed text and numbers, returning a specific value (X) if it contains at least one of several target substrings (F or 5), and a different value (NA) if none are found.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Searching for multiple specific text strings or numbers inside mixed-content cells using conditional logic.
- Observed behavior
- The formula correctly outputs 'X' when either 'F' or '5' is present anywhere in the target string, and outputs 'NA' when neither value is detected.
Ensure your target column contains the data you want to evaluate. Note that the SEARCH function used in these solutions is case-insensitive; if you require an exact case match, you will need to substitute it with the FIND function.
Use IF, OR, ISNUMBER, and SEARCH Functions
This is the most standard and reliable approach. It checks for each substring individually using SEARCH and triggers the OR logic if any of the searches return a valid number.
The SEARCH function looks for your target text and returns its starting position as a number. If the text isn't found, it returns an error. The ISNUMBER function converts this position into a TRUE or FALSE logical value, which is then evaluated by the OR function.
Click on the empty cell where you want the result to be displayed (for example, E2).
Type the formula: =IF(OR(ISNUMBER(SEARCH("F",$D2)),ISNUMBER(SEARCH("5",$D2))),"X","NA") into the formula bar.
Press Enter to see the result for the first row. Then, click and drag the fill handle (the small square at the bottom-right corner of the cell) down the column to copy the formula to the remaining cells.
Use an Array Constant with SUM and ISNUMBER
A more compact method that evaluates an array of criteria at once. This is especially useful if you have a large list of text strings to look for.
Handle Complex Formulas Seamlessly with WPS Office
WPS Spreadsheet fully supports advanced array functions, conditional logic, and all standard Excel formulas including IF, OR, SEARCH, and ISNUMBER. It provides a robust, intuitive environment for data analysts and everyday users.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your data.
- 2. Use formula autocomplete: Select your target cell and type '=', then begin typing 'IF'. Select the function from the dropdown suggestions.
- 3. Input and apply: Type your full conditional formula. Press Enter, then drag the fill handle to apply it across your entire data range.
Frequently Asked Questions
How can I make the multiple text search case-sensitive?
To perform a case-sensitive search, replace the SEARCH function with the FIND function in your formula. For example, use ISNUMBER(FIND("F",$D2)). FIND strictly matches the case, meaning 'F' will not match 'f'.
What if I want to check if ALL criteria are met instead of just one?
If you need all conditions to be true simultaneously, change the OR function to an AND function. Your formula would start with =IF(AND(ISNUMBER(SEARCH(...)), ISNUMBER(SEARCH(...))), "X", "NA").
Will this formula work if there are extra spaces in the cell?
Yes, the SEARCH function looks for the designated text string anywhere within the cell, regardless of leading, trailing, or middle spaces around the characters.
Can I search for longer words instead of single characters?
Absolutely. You can replace the single characters with full words or phrases. For instance, replacing "F" with "Completed" inside the quotation marks will check if the word 'Completed' exists in the cell.
