How to Highlight Matching Text in Excel Cells Using Conditional Formatting in WPS Office
Question details
The user wants to find allowed ingredients that appear inside cells containing multiple ingredients or lines of text and highlight the matching content, not just format the entire cell.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is reviewing a spreadsheet where each cell contains multiple ingredients and needs to compare each ingredient against an allowed-ingredient list so matching items can be identified quickly.
- Observed behavior
- Standard conditional formatting highlights the entire cell when it detects a match, but it does not apply formatting only to the matching part of the text within the cell.
Prepare the allowed-ingredient list in a separate range or sheet, and keep the separator inside each cell consistent, such as a comma or line break. This helps formula-based rules and helper-column comparisons work reliably.
Use formula-based conditional formatting to highlight cells containing a match
This native WPS Spreadsheets method is the easiest way to flag every cell that contains any allowed ingredient, even when a cell has multiple ingredients.
Conditional formatting evaluates the entire cell value and applies the selected fill to the whole cell. It cannot limit the fill to one ingredient inside the cell, but it gives a fast visual filter for rows that need review.
The formula uses SEARCH and SUMPRODUCT to check the cell text against the allowed-ingredient range without case sensitivity.
Open your workbook in WPS Office Spreadsheets, then click and drag to select the cells that contain the multi-ingredient text, for example A2:A100.
Go to the Home tab, click Conditional Formatting, and choose New Rule.
In the New Formatting Rule dialog, select Use a formula to determine which cells to format, then enter =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$10,A2)))>0 where D2:D10 is the allowed-ingredient list and A2 is the first selected cell.
Click Format, open the Fill tab, choose a highlight color, and click OK to apply the rule.
Add a helper column to identify the exact allowed ingredient
Use this when you need to see which allowed ingredient matched in each row, not only that a match exists.
Highlight cells containing matching ingredients with WPS Office Spreadsheets
Use WPS Office Spreadsheets to create a formula-based conditional formatting rule that flags cells containing any allowed ingredient. Add a helper column to identify the exact match, and save as a .xlsx file that remains compatible with Microsoft Excel.
- 1. Open your workbook in WPS Office Spreadsheets: Launch WPS Office, open the Excel or WPS workbook, and confirm the allowed-ingredient list is in a separate range such as D2:D10.
- 2. Select the ingredient text cells: Click and drag to select the cells that contain multiple ingredients or lines of text.
- 3. Create a formula-based conditional formatting rule: On the Home tab, click Conditional Formatting > New Rule, choose Use a formula, and enter =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$10,A2)))>0.
- 4. Apply the highlight format: Click Format, choose a fill color on the Fill tab, and confirm to highlight every cell that contains an allowed ingredient.
- 5. Save the workbook: Save the file as .xlsx to keep the conditional formatting rule compatible with Microsoft Excel and WPS Office.
Frequently Asked Questions
Can WPS conditional formatting highlight only part of the text inside a cell?
No. Standard conditional formatting works at the cell level, so it applies the fill to the whole cell when the rule returns true. To emphasize only the matching text, you can use a helper column to find the match and then manually color the individual text with the font color option.
What formula highlights cells that contain any item from an allowed list?
In a formula-based conditional formatting rule, use =SUMPRODUCT(--ISNUMBER(SEARCH($D$2:$D$10,A2)))>0. Replace D2:D10 with your allowed list and A2 with the first selected cell.
Why is my conditional formatting rule highlighting the wrong cells?
Check that the allowed-list range uses absolute references such as $D$2:$D$10 and that the formula points to the first cell of the selected range. Also make sure there are no extra spaces or inconsistent separators in the ingredient text.
Can I save this workbook and open it in Microsoft Excel?
Yes. WPS Office Spreadsheets saves to the .xlsx format, and formula-based conditional formatting rules are preserved when the file is opened in Excel.
What if ingredients are separated by line breaks instead of commas?
The SEARCH formula still works because line breaks are part of the cell text, but if you later use a macro or text-splitting formula, use the correct delimiter such as CHAR(10) for line breaks.
