×

Excel Formula To Find Any Special Character

By Chris Newman •  Updated: 11/10/23 •  7 min read
How to write an Excel formula to identify special characters

Is There A Formula to Find Special Characters in Excel?

There are a few different ways you can approach determining if a string value inside an Excel cell contains a special character (e.g. !@#$%^&*). However, until Microsoft decides to give us access to RegEx-abled functions within Excel, the formulas to solve this are a bit complex.

Let's look at a few ways to output either a True/False value based on whether a cell contains a special character.

Find Special Characters Formula

Excel Formula That Searches For Special Characters

To determine if the text in cell A1 contains a special character using an Excel formula, you can utilize the SUMPRODUCT function along with the ISNUMBER and SEARCH functions. This method checks for the presence of any non-standard characters by specifying a range of special characters to look for within the text in A1.

Here's a formula example that checks for a set of common special characters:

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

This formula works by searching for each special character within cell A1. The SEARCH function looks for each character specified in the array and returns the position number if found, or an error if not.

Note that the SEARCH function utilizes a few wildcard characters in Excel – the question mark (?), asterisk (*), and tilde (~). For this reason, you'll notice the formula has the tilde (~) before each of these 3 special characters to negate their wildcard abilities.

The ISNUMBER function then converts these positions to TRUE (if a character is found) and errors to FALSE. The double unary operator (--) converts the TRUE/FALSE values to 1/0, and SUMPRODUCT sums these numbers up. If the sum is greater than 0, it means at least one special character was found, and the formula returns TRUE. Otherwise, it returns FALSE.

Keep in mind that you might need to adjust the list of special characters based on your specific needs. This formula covers a wide range of common special characters but might not include every possible character you're interested in.

Screentip and Supertip example in Excel.

If you would like to spice your formula up a bit, I like to use a checkmark or X in place of the TRUE/FALSE formula results. To do this, simply wrap an IF function around the special character search function and include a checkmark or X result.

Here's how it might look for you:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH({"!","@","#","$","%","^","&","~*","(",")","_","+","=","{","}","[","]","|","\",":",";","'","""","<",">",",",".","~?","/","~~","`"}, A1)))>0,"✔","✖")

Screentip and Supertip example in Excel.

UDF VBA Function To Search For Special Characters

If you are open to coding in VBA, you can create a UDF (User Defined Function) that can be called within your spreadsheet. I'll provide you with a couple of examples of what can be written to search for special characters within cell text.

UDF To Find Special Characters Using Regular Expression

This function aims to determine whether the value of a given cell includes any character that is not a standard letter (A-Z, a-z) or digit (0-9). It is designed to be used as a User Defined Function (UDF) within Excel, allowing users to apply this check to cells directly from the spreadsheet.

This function can be directly used in Excel spreadsheets to flag cells containing special characters. For example, by entering =ContainSpecialChar(A1) in a cell, the function will evaluate the value of cell A1 and return TRUE if any special characters are found, or FALSE otherwise.

Function ContainSpecialChar(cell As Range) As Boolean 'PURPOSE: Determine if Cell Value contains a special character 'SOURCE: www.TheSpreadsheetGuru.com Dim RegEx As Object Set RegEx = CreateObject("VBScript.RegExp") RegEx.Global = True RegEx.Pattern = "[^A-Za-z0-9]" ContainSpecialChar = (RegEx.Replace(cell.Value, "") <> cell.Value) End Function

A super tip text example in Excel's Ribbon.

The function utilizes a regular expression (RegEx) object from the VBScript engine, which is accessible in VBA through the CreateObject function. This object is configured to globally search the cell's value for any characters that do not match the standard alphanumeric characters (specified by the pattern [^A-Za-z0-9]).

The pattern [^A-Za-z0-9] is a regular expression that matches any character that is not a letter (either uppercase or lowercase) or a digit. The caret (^) inside the square brackets negates the character class, targeting any character outside the specified ranges.

The RegEx.Replace method is used to replace all occurrences of non-alphanumeric characters (as defined by the RegEx pattern) in the cell's value with an empty string (""). By comparing the result of this replacement to the original cell value, the function can determine if there were any special characters present. If the result of the replacement is different from the original value.

RegEx.Replace(cell.Value, "") <> cell.Value - This line tests to see if the cell truly contains special characters by seeing if the two string values equal each other. The Boolean test results in the function either returning a TRUE or FALSE value.

UDF Code To Identify Text Containing Special Characters via Looping

The primary goal of this function is to inspect the content of a cell for any character that falls outside the typical ranges of letters and numbers. It achieves this by examining each character in the cell's value individually.

Function HasSpecialChar(cell As Range) As Boolean
'PURPOSE: Determine if Cell Value contains a special character
'SOURCE: www.TheSpreadsheetGuru.com

Dim x As Integer
Dim char As String

'Loop through each character in cell
  For x = 1 To Len(cell.Value)
    char = Mid(cell.Value, x, 1)
    
    'Test if character is special
      If Not (char Like "[0-9a-zA-Z]") Then
        HasSpecialChar = True
        Exit Function
      End If
    
  Next x

HasSpecialChar = False

End Function

HasSpecialChar VBA UDF

The function iterates over each character in the cell's value using a For loop, which runs from the first character to the last character, based on the length of the cell's value: Len(cell.Value).

Within the loop, it uses the MID function to extract each character (char) one by one by its position (x) in the cell's value.

For each character extracted, the function checks whether it is not an alphanumeric character using the Like operator with the pattern "[0-9a-zA-Z]". This pattern defines the set of characters considered to be non-special, i.e., digits 0-9 and letters A-Z (both uppercase and lowercase).

If a character is found that does not match this pattern, indicating it is a special character, the function immediately returns True and terminates (Exit Function). This means the cell contains at least one character outside the standard alphanumeric range, fulfilling the function's criteria for having a special character.

If the loop completes without finding any special characters, the function concludes that all characters in the cell are alphanumeric and returns False.


I Hope This Microsoft Excel Article Helped!

Hopefully, I was able to explain how you can write a formula to identify if a cell's value contains at least one special character. If you have any questions about this technique or suggestions on how to improve it, please let me know in the comments section below.

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.