Function Problems

How to Combine Excel Conditional Formatting Rules for Partial Text

Maira Mehtab Oct 12, 2026 870 views

Question details

The user wants to format rows based on multiple partial text matches, such as different city names, using a single combined conditional formatting rule rather than creating separate rules for each condition.

How to Combine Excel Conditional Formatting Rules to Match Partial Text

Product: Excel
Device & OS: not provided
Scenario: Applying conditional formatting to a dataset where multiple specific text strings need to trigger the same color formatting.
Observed behavior: The user successfully created a rule for a single partial match but is unable to merge multiple conditions into a single combined formatting rule.

Before you start

Identify the exact column that contains the text strings you want to evaluate and determine whether the text will always appear at the beginning of the cell or anywhere within the cell.

Solution 1Recommended

Use the OR and LEFT Functions for Text at the Beginning

Combine multiple conditions using the OR function alongside LEFT when your target partial text always appears at the start of the cell.

By nesting multiple LEFT function checks inside a single OR function, Excel evaluates all your conditions simultaneously. If any one of the specified text segments matches, the formatting is applied.

Select the Data Range

Highlight the range of cells or entire rows you want to apply the conditional formatting to. Ensure the active cell matches the row referenced in your formula.

Open Conditional Formatting

Navigate to the 'Home' tab on the ribbon, click 'Conditional Formatting', and select 'New Rule' from the dropdown menu.

Choose Formula Option

In the dialog box, select 'Use a formula to determine which cells to format'.

Enter the Combined Formula

Type your formula using OR and LEFT. For example: =OR(LEFT($F2,7)="Jiříkov", LEFT($F2,6)="Krásná"). Adjust the cell references, character counts, and text to match your actual dataset.

Apply Formatting

Click the 'Format' button, choose your desired fill color or font styling, click 'OK', and then click 'OK' again to apply the rule.

Use the OR and LEFT Functions for Text at the Beginning

Regional Settings Adjustment: If your system's regional settings use a comma as the decimal separator, Excel expects semicolons (;) instead of commas (,) to separate formula arguments.

Solution 2

Use ISNUMBER and SEARCH for Text Anywhere in the Cell

Use this formula combination if the target partial text can appear in the middle or at the end of the cell value.

WPS Spreadsheet Capabilities

Combine Conditional Formatting Rules Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced formula-based conditional formatting. You can seamlessly use OR, LEFT, and SEARCH functions to evaluate partial text exactly as you would in Microsoft Excel.

1. Open Your Data: Launch WPS Spreadsheet and select the data range you wish to conditionally format.
2. Access Conditional Formatting: Under the 'Home' tab, click on 'Conditional Formatting' and choose 'New Rule'.
3. Select Formula Rule: Click on 'Use a formula to determine which cells to format'.
4. Enter the Condition: Type your combined OR formula, such as =OR(LEFT($A2,4)="City", ISNUMBER(SEARCH("Town",$A2))).
5. Format and Save: Click 'Format' to define the styling rules, then click 'OK' to instantly apply it to your dataset.

100% compatible with Microsoft Excel formatting rules and formulas.Lightweight software that processes complex conditional rules smoothly.Intuitive graphical interface for managing and editing multiple formatting rules.Free to use for everyday advanced spreadsheet calculations.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting formula highlighting the wrong rows?

This usually happens due to incorrect absolute or relative cell references. Ensure you lock the column reference (e.g., $F2) by using the dollar sign, but leave the row reference relative so the formula evaluates each subsequent row correctly.

Can I make the partial text match case-sensitive?

Yes. The SEARCH function is not case-sensitive. If you need an exact case-sensitive match, replace SEARCH with the FIND function in your ISNUMBER formula.

What if I have more than 10 different city names to match?

If you have a large list of text fragments, a long OR formula becomes difficult to manage. Instead, type your target cities in a separate named range and use an array formula with the COUNTIF or SUMPRODUCT function to check against the entire list at once.

Why does Excel throw an error when I enter my OR formula?

This is often due to a mismatch in regional settings. If your computer uses a comma as a decimal separator, Excel formulas require semicolons (;) instead of commas (,) to separate the arguments within the OR function.

Excel & Data Problems