With the help of a custom rule, you can highlight any cell, column, or row that contains special characters in Google Sheets. Special characters refer to any non-alphanumeric characters, and I’ll explain more about that shortly.

In this tutorial, any characters other than letters, numbers, and spaces fall under the special character category. However, you can modify my custom formula to set exceptions for specific characters.

Examples of special characters include:

; < = >  [ \ ] ^ _ ` { | } ~ ? @ ! & " # $ %  ' ( ) * + , - . :

All of the above characters, as well as foreign characters, are considered special characters. For instance, in the name “Anne Brontë,” the character “ë” is treated as a special character.

To highlight cells containing special characters, I use a combination of the following functions: LEN, TRIM, and REGEXREPLACE. The key function here is REGEXREPLACE.

Let’s first apply a conditional formatting rule using my custom formula to highlight any cells that contain special characters. Afterward, I’ll explain the formula in detail.

Highlighting Cells that Contain Special Characters

To highlight cells containing special characters, use the following formula:

=LEN(TRIM(REGEXREPLACE(A1, "[A-Za-z0-9]", "")))

Replace A1 with the first cell in your range. For example, if you want to highlight cell B10, replace A1 with B10. If you want to highlight all cells in a range, such as C1:E20, replace A1 with C1—the first cell.

Follow these steps to apply the rule:

1. Select the range of cells you want to highlight.
2. Click Format > Conditional formatting.
3. Under Format cells if…, select Custom formula is from the drop-down menu and enter the formula in the provided field.
4. Set your color preferences and click Done.

Highlighting Entire Rows or Columns with Special Characters

Sometimes, you may want to match special characters in a column and highlight entire rows wherever those special characters appear. In that case, you can use the following formula:

=LEN(TRIM(REGEXREPLACE(A$1, "[A-Za-z0-9]", "")))

This formula will locate any characters other than alphanumeric characters in row 1 and highlight the corresponding columns.

For example, if it finds matches in D1 and E1, it will highlight D1:D and E1:E, thereby highlighting the entire columns.

You can replace A$1 with the first cell in the specified range.

To highlight rows containing special characters in column A, use this formula:

=LEN(TRIM(REGEXREPLACE($A1, "[A-Za-z0-9]", "")))

For instance, if cell A10 contains non-alphanumeric characters, this formula will highlight the entire row 10.

Again, remember to replace $A1 with the first cell in the range you want to highlight.

How Do These Highlight Rules Work?

The highlight rules have a very simple logic.

The REGEXREPLACE function replaces all the alphanumeric characters in cell A1 with an empty string (""). The TRIM function then removes any leading or trailing whitespace. The role of LEN is to return the length of the special characters that remain.

The rule highlights cells wherever the formula returns a number greater than 0.

How to Exclude Specific Special Characters?

This is simple. Sometimes you may want to exclude special characters like the underscore (_) and @ symbol from this rule. In such cases, you can modify the formula as follows:

=LEN(TRIM(REGEXREPLACE(A1, "[A-Za-z0-9@_]", "")))

Resources

• Restrict Entering Special Characters in Google Sheets (Data Validation)
• Insert Special Characters Without Add-on in Google Sheets
• Inserting Bullet Points in Google Sheets