With the help of a single piece of formula, you can highlight any cell, column, row or even an array that contain any special characters in Google Doc Spreadsheets. Special characters here means any non-alphanumeric characters. I will come to that. This Google Sheets tutorial is for those who want to know how to highlight cells containing special characters in Google Sheets.

As per this tutorial, any characters besides text, numeric and white space are coming under the special character category. But of course, you can set exceptions in the rule by modifying my custom formula.

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

All of the above characters and even foreign characters are not allowed. In the name “Anne Brontë“, we treat the highlighted character as a special character.

Here I’m using the combination of following functions to highlight cells containing Special Characters in Google Sheets. The functions in use are LEN, TRIM, and REGEXREPLACE. In this, of course, the Regexreplace is the main formula.

First, let’s see how to apply conditional formatting rule with my custom formula to highlight any cells that contain special characters. Then you can see the formula explanation.

Highlight Cells Containing Special Characters in Google Sheets

First I am applying the formula in a single cell.

Highlighting a Single Cell That Contains Special Character

=len(trim(REGEXREPLACE(A1, "[A-Za-z0-9]", "")))

Steps:

1. Select Cell A1.

2. Go to the menu Format > Conditional formatting.

3. There “Apply to range” is A1.

4. Under “format cells if…” select “Custom formula is …” from the drop-down menu and type the above formula in the filed provided.

5. Set your color preferences and click “Done”.

In Google Sheets How to Highlight an Entire Column That Contains Special Characters

Learn here how to highlight an entire column (infinitive range) that contain special characters in Google Sheets. Here also the conditional formatting formula is the same. The only change is in the range.

=len(trim(REGEXREPLACE(A1:A, "[A-Za-z0-9]", "")))

In conditional formatting, in the above steps, point # 3, change the range from A1 to A1: A

How to Highlight Rows in Google Sheets That Contain Special Characters

It all depends on how you change the range in the formula and in the conditional formatting rules.

For example, if you want to highlight the row range A1: H1, that contain special characters, the formula and conditional formatting rules should be as follows.

=len(trim(REGEXREPLACE(A1:H1, "[A-Za-z0-9]", "")))

First, you may select the range A1: H1. Then go to conditional formatting. Make sure that the “apply to range” is A1: H1. Then apply the above formula as per the previous example.

Steps to Highlight an Array That Contain Special Characters

Here the formula is different. Here I’m applying the conditional formatting rules in the range A1: H4. This time you should use an ArrayFormula as below as there are multiple rows and columns involved.

=ArrayFormula(len(trim(REGEXREPLACE(A1:H4, "[A-Za-z0-9]", ""))))

To know how to apply this custom formula, see the screenshot below.

Now to the final part of this tutorial. Here you have already learned how to highlight Cells containing special characters in Google Sheets. Now you may have interested to know how this formula works.

=len(trim(REGEXREPLACE(A1, "[A-Za-z0-9]", "")))

This is our first formula with a single cell reference which is Cell A1. This formula replaces all the alphanumeric characters in Cell A1 with"", then trim the white spaces, if any, and return the length of the special characters which is left. If you have the following word, my twitter handle, in Cell A1, it returns the number 2.

@prashanth_kv

Formula Breakup:

Formula

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

Result

@_

This formula returns all the special characters in Cell A1 by removing or replacing alphanumeric with"".

I’ve used the TRIM function to remove any white spaces that exist. In this case, there are no white spaces.

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

Finally, the LEN function returns the length of the characters left. In the above example with my twitter handle in cell A1, the formula returns #2. If there is no special character in the Cell, the formula would return blank. In conditional formatting, this (blank cell) causes #VALUE! error. Such cells excluded in the formatting. In other words, if the formula returns any number as length, that means the cell contains the special character(s) and the conditional formatting applied to that cell. Rest of the cells excluded.

Can We Set Exception to the Above Conditional Formatting Rule and Include Selected Special Characters?

Yes, we can. This is simple. Sometimes you may want to exclude special characters like underscore and @ symbols from this rule. In such cases, you can modify the formula as below.

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

That’s all. Follow this tutorial carefully to learn how to successfully highlight Cells containing Special Characters in Google Sheets. Enjoy!