Formula Errors

How to Use Excel Conditional Formatting Formulas for Text, Numbers, Symbols, and Emojis

Huda Qurayshi Oct 12, 2026 871 views

Question details

The user needs to apply conditional formatting rules to categorize cells by content type (symbols, emojis, letters, numbers, blanks) as an alternative to REGEXMATCH.

Excel Conditional Formatting Formulas for Text, Numbers, Symbols, and Emojis

Product: Microsoft Excel
Device & OS: not provided
Scenario: Applying conditional formatting to differentiate between cells containing purely alphanumeric text and cells containing special symbols or emojis.
Observed behavior: Without native REGEXMATCH support in standard Excel versions, users cannot directly filter by regular expressions and must evaluate cell contents character-by-character to apply formatting.

Before you start

Ensure you are using an Excel version that supports dynamic arrays (like the LET function), or be prepared to use traditional array formulas. Note that processing every character in very large datasets may impact spreadsheet performance.

Solution 1Recommended

Use Character Evaluation Formulas for Conditional Formatting

Create custom conditional formatting rules using a combination of LET, SUMPRODUCT, MID, and UNICODE to evaluate each character instead of using REGEXMATCH.

Since older versions of Excel lack a direct equivalent to REGEXMATCH, you must inspect the string manually. By using the MID function to split the text and the UNICODE function to check the numerical value of each character, you can determine if a cell contains standard letters/numbers or special symbols/emojis.

Select the target data range

Highlight the range of cells you want to apply the conditional formatting to (for example, A2:A100).

Open the Conditional Formatting menu

Go to the Home tab on the ribbon, click on 'Conditional Formatting', and select 'New Rule'.

Select the formula option

In the New Formatting Rule dialog, click on 'Use a formula to determine which cells to format'.

Enter the validation formula

Enter your formula using LET, MID, and UNICODE. To check for non-alphanumeric characters, the formula evaluates if the UNICODE values fall outside standard letter and number ranges.

Format and organize rule hierarchy

Click the 'Format' button to choose your highlight color. Once saved, go to 'Manage Rules' to ensure the letters-and-numbers rule is at the top, blank cells are excluded, and the symbols/emojis rule is applied only when the cell contains no standard letters or numbers.

Use Character Evaluation Formulas for Conditional Formatting

Rule Precedence: Always set the rule for standard letters and numbers first and check 'Stop If True' for blank cells. Apply the symbols-or-emojis rule last to prevent formatting conflicts in mixed-content cells.

Easily Apply Advanced Conditional Formatting in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas and conditional formatting, making it effortless to identify and highlight specific data types like text, numbers, symbols, and emojis without complex setups.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open your workbook containing the data you want to format.
2. Highlight the cell range: Select the cells you want to evaluate for text, numbers, symbols, or emojis.
3. Access Conditional Formatting: Navigate to the Home tab and click the 'Conditional Formatting' icon, then choose 'New Rule'.
4. Apply a custom formula: Select 'Use a formula', input your validation array formula, and customize the background color or font style.

Free, lightweight, and fast office suiteSeamless compatibility with Microsoft Excel formulas and .xlsx filesIntuitive user interface for managing conditional formatting rulesBuilt-in support for advanced functions like LET and UNICODE

microsoft office alternative - wps office

Frequently Asked Questions

Can I use REGEXMATCH natively in standard Excel conditional formatting?

No, standard Excel versions do not natively support REGEXMATCH. You must use combinations of MID, CODE, and UNICODE functions to evaluate strings, or rely on VBA/Office Scripts for regular expression capabilities.

How do I exclude blank cells from my conditional formatting rules?

You can exclude blank cells by adding a specific rule for them. Create a rule using the formula =ISBLANK(A1), set no formatting format, and check the 'Stop If True' box in the Manage Rules dialog.

Why shouldn't I use the CODE function to identify emojis?

The CODE function only supports the standard ANSI character set (up to 255). Emojis and complex symbols fall outside this range. You must use the UNICODE function, which supports the extended universal character set.

Excel & Data Problems