Table of Contents
In Excel, you can search for special characters in a cell by using the Find and Replace tool. This tool allows you to search for any character, including special characters, by specifying the character in the Find What field. When searching for special characters, you must use the ASCII code for the character in the Find What field. Once the character has been specified, Excel will search the cell for the character and highlight any instances in the cell.
You can use the following formula to check if a given cell in Excel contains any special characters anywhere in the cell:
=SUMPRODUCT(--ISNUMBER(SEARCH({"!","#","$","%","(",")","^","@","[","]","{","}"},A2)))>0
This particular formula checks if cell A2 contains any special characters and returns TRUE if it does.
Otherwise, it returns FALSE.
The following example shows how to use this formula in practice.
Example: Search for Special Characters in Cell in Excel
Suppose we have the following list of phrases in Excel:
Suppose we would like to search each phrase in column A to determine if it contains a special character or not.
To do so, we can type the following formula into cell B2:
=SUMPRODUCT(--ISNUMBER(SEARCH({"!","#","$","%","(",")","^","@","[","]","{","}"},A2)))>0
We can then click and drag this formula down to each remaining cell in column B:
The formula returns TRUE if the phrase contains a special character.
Otherwise, it returns FALSE if no special characters are found.
For example, we can see:
- The first phrase contains at least one special character.
- The second phrase does not contain any special characters.
- The third phrase contains at least one special character.
Note that the formula we used contains the most common special characters but if there are other special characters you’d like to search for, simply include those within the SEARCH() function in the formula.
Also note that you can choose to only search for specific special characters if you’d like.
For example, you could use the following formula to only search for a dollar sign or percent sign in cells:
=SUMPRODUCT(--ISNUMBER(SEARCH({"$","%"},A2)))>0
This formula would return TRUE if a dollar sign ($) or percent sign (%) were detected in a given cell or FALSE if neither character was detected.
How to Search for an Asterisk in a Cell in Excel
How to Search for a Question Mark in Excel