How to Use Conditional Formatting for Cells Containing Two Partial Text Strings
Question details
The user needs to highlight cells within a specific range that contain two independent partial text strings (e.g., 'James' and 'retired') using a case-insensitive conditional formatting rule.
- Product
- Spreadsheets
- Device & OS
- not provided
- Scenario
- Filtering or highlighting specific records in a large dataset where cells must match multiple partial text criteria simultaneously.
- Observed behavior
- The user wants to format cells where both text conditions are met, ensuring the formula searches for each string independently regardless of character case.
Before applying the conditional formatting rule, ensure your target cell range (e.g., B2:B1000) is clearly defined and note the top-left cell of your selection, as it will be used in the formula.
Use a Combined AND, ISNUMBER, and SEARCH Formula
Apply a custom formula in the conditional formatting rules to identify cells containing both specific text strings independently.
The SEARCH function is case-insensitive and locates the position of a substring within a text string. By combining it with ISNUMBER, you can return a TRUE or FALSE value depending on whether the text is found. Wrapping multiple of these conditions inside an AND function ensures the formatting only applies when both text strings are present in the cell.
Highlight the range of cells you want to format in your worksheet. For example, select the range B2:B1000.
Navigate to the 'Home' tab on the top ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown menu.
Choose the option 'Use a formula to determine which cells to format'. In the formula box, enter: =AND(ISNUMBER(SEARCH("James",B2)),ISNUMBER(SEARCH("retired",B2)))
Click the 'Format' button to choose your desired highlight color or font style, click 'OK' to confirm the format, and then click 'OK' again to apply the rule.
Highlight Data Effortlessly with WPS Spreadsheet
WPS Spreadsheet fully supports advanced conditional formatting formulas, allowing you to quickly analyze and highlight cells based on complex criteria like multiple partial text strings.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet file containing the target dataset.
- 2. Select Your Data: Highlight the specific column or range of cells (e.g., B2:B1000) that you want to apply the rule to.
- 3. Apply Conditional Formatting: Go to the Home tab, click 'Conditional Formatting' > 'New Rule', and input your AND/ISNUMBER/SEARCH formula.
- 4. Customize the Appearance: Set a distinct fill color or font weight so you can instantly spot the cells meeting both conditions.
Frequently Asked Questions
Can I make the conditional formatting case-sensitive?
Yes. If you need the search to be case-sensitive, replace the SEARCH function in your formula with the FIND function. The updated formula would be: =AND(ISNUMBER(FIND("James",B2)),ISNUMBER(FIND("retired",B2))).
How do I format cells if they contain EITHER of the two text strings?
To highlight cells that contain at least one of the target strings, replace the AND function with the OR function. The formula will look like this: =OR(ISNUMBER(SEARCH("James",B2)),ISNUMBER(SEARCH("retired",B2))).
Why is my conditional formatting formula not working correctly across the entire range?
This is typically caused by absolute cell referencing. Ensure the cell reference in your formula (e.g., B2) points to the top-left cell of your selection and does not contain dollar signs (like $B$2). Using relative references allows Excel or WPS to automatically update the row number as it evaluates each cell.
Does this formula work if the two words appear in a different order in the cell?
Yes. Because the formula searches for each string completely independently using separate SEARCH functions wrapped in an AND function, it will highlight the cell correctly regardless of which word appears first.
