How to delete special / unwanted characters in Excel (2023)

In this article, you will learn how to delete specific characters from a text string and remove unwanted characters from multiple cells at once.

When importing data to Excel from somewhere else, a whole lot of special characters may travel to your worksheets. What's even more frustrating is that some characters are invisible, which produces extra white space before, after or inside text strings. This tutorial provides solutions for all these problems, sparing you the trouble of having to go through the data cell-by-cell and purge unwanted characters by hand.

• Remove special character from string
• Delete multiple characters
• Eradicate all unwanted characters at once with LAMBDA
• Strip special characters with VBA functions
• Remove non-printable characters
• Delete special characters with Ultimate Suite

Remove special character from Excel cell

To delete a specific character from a cell, replace it with an empty string by using the SUBSTITUTE function in its simplest form:

SUBSTITUTE(cell, char, "")

For example, to eradicate a question mark from A2, the formula in B2 is:

=SUBSTITUTE(A2, "?", "")

To remove a character that is not present on your keyboard, you can copy/paste it to the formula from the original cell.

For instance, here's how you can get rid of an inverted question mark:

=SUBSTITUTE(A2, "¿", "")

But if an unwanted character is invisible or does not copy correctly, how do you put it in the formula? Simply, find its code number by using the CODE function.

In our case, the unwanted character ("¿") comes last in cell A2, so we are using a combination of the CODE and RIGHT functions to retrieve its unique code value, which is 191:

=CODE(RIGHT(A2))

Once you get the character's code, serve the corresponding CHAR function to the generic formula above. For our dataset, the formula goes as follows:

=SUBSTITUTE(A2, CHAR(191),"")

Note. The SUBSTITUTE function is case-sensitive, meaning it treats lowercase and uppercase letters as different characters. Please keep that in mind if your unwanted character is a letter.

Delete multiple characters from string

In one of the previous articles, we looked at how to remove specific characters from strings in Excel by nesting several SUBSTITUTE functions one into another. The same approach can be used to eliminate two or more unwanted characters in one go:

SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(cell, char1, ""), char2, ""), char3, "")

For example, to eradicate normal exclamation and question marks as well as the inverted ones from a text string in A2, use this formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, "!", ""), "¡", ""), "?", ""), "¿", "")

The same can be done with the help of the CHAR function, where 161 is the character code for "¡" and 191 is the character code for "¿":

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3, "!", ""), "?", ""), CHAR(161), ""), CHAR(191), "")

Nested SUBSTITUTE functions work fine for a reasonable number of characters, but if you have dozens of characters to remove, the formula becomes too long and difficult to manage. The next example demonstrates a more compact and elegant solution.

Remove all unwanted characters at once

The solution only works in Excel for Microsoft 365

As you probably know, Excel 365 has a special function that enables you to create your own functions, including those that calculate recursively. This new function is named LAMBDA, and you can find full details about it in the above-linked tutorial. Below, I'll illustrate the concept with a couple of practical examples.

A custom LAMBDA function to remove unwanted characters is as follows:

=LAMBDA(string, chars, IF(chars<>"", RemoveChars(SUBSTITUTE(string, LEFT(chars, 1), ""), RIGHT(chars, LEN(chars) -1)), string))

To be able to use this function in your worksheets, you need to name it first. For this, press Ctrl + F3 to open the Name Manager, and then define a New Name in this way:

1. In the Name box, enter the function's name: RemoveChars.
2. Set the scope to Workbook.
3. In the Refers to box, paste the above formula.
4. Optionally, enter the description of the parameters in the Comments box. The parameters will be displayed when you type a formula in a cell.
5. Click OK to save your new function.

For the detailed instructions, please see How to name a custom LAMBDA function.

Once the function gets a name, you can refer to it like any native formula.

From the user's viewpoint, the syntax of our custom function is as simple as this:

RemoveChars(string, chars)

Where:

• String - is the original string, or a reference to the cell/range containing the string(s).
• Chars - characters to delete. Can be represented by a text string or a cell reference.

For convenience, we input unwanted characters in some cell, say D2. To remove those characters from A2, the formula is:

=RemoveChars(A2, $D$2)

For the formula to work correctly, please take notice of the following things:

• In D2, characters are listed without spaces, unless you wish to eliminate spaces too.
• The address of the cell containing the special characters is locked with the $ sign ($D$2) to prevent the reference from changing when coping the formula to the below cells.

And then, we simply drag the formula down and have all the characters listed in D2 deleted from cells A2 through A6:

To clean multiple cells with a single formula, supply the range A2:A6 for the 1st argument:

=RemoveChars(A2:A6, D2)

Since the formula is entered only in the top-most cell, you needn't worry about locking the cell coordinates - a relative reference (D2) works fine in this case. And due to support for dynamic arrays, the formula spills automatically into all the referenced cells:

Removing a predefined character set

To delete a predefined set of characters from multiple cells, you can create another LAMBDA that calls the main RemoveChars function and specify the undesirable characters in the 2^nd parameter. For example:

To delete special characters, we've created a custom function named RemoveSpecialChars:

=LAMBDA(string, RemoveChars(string, "?¿!¡*%#@^"))

To remove numbers from text strings, we've created one more function named RemoveNumbers:

=LAMBDA(string, RemoveChars(string, "0123456789"))

Both of the above functions are super-easy to use as they require just one argument - the original string.

To eliminate special characters from A2, the formula is:

=RemoveSpecialChars(A2)

To delete only numeric characters:

=RemoveNumbers(A2)

How this function works:

In essence, the RemoveChars function loops through the list of chars and removes one character at a time. Before each recursive call, the IF function checks the remaining chars. If the chars string is not empty (chars<>""), the function calls itself. As soon as the last character has been processed, the formula returns string it its present form and exits.

For the detailed formula break down, please see Recursive LAMBDA to remove unwanted characters.

Remove special characters with VBA

The functions work in all versions of Excel

If the LAMBDA function is not available in your Excel, nothing prevents you from creating a similar function with VBA. A user-defined function (UDF) can be written in two ways.

Custom function to delete special characters recursive:

This code emulates the logic of the LAMBDA function discussed above.

Custom function to remove special characters non-recursive:

Here, we cycle through unwanted characters from 1 to Len(chars) and replace the ones found in the original string with nothing. The MID function pulls unwanted characters one by one and passes them to the Replace function.

Insert one of the above codes in your workbook as explained in How to insert VBA code in Excel, and your custom function is ready for use.

Not to confuse our new user-defined function with the Lambda-defined one, we've named it differently:

RemoveUnwantedChars(string, chars)

Assuming the original string is in A2 and unwelcome characters in D2, we can get rid of them using this formula:

= RemoveUnwantedChars(A2, $D$2)

Custom function with hardcoded characters

If you do not want to bother about supplying special characters for each formula, you can specify them directly in the code:

Please keep in mind that the above code is for demonstration purposes. For practical use, be sure to include all the characters you want to delete in the following line:

chars = "?¿!¡*%#$(){}[]^&/\~+-"

This custom function is named RemoveSpecialChars and it requires just one argument - the original string:

RemoveSpecialChars(string)

To strip off special characters from our dataset, the formula is:

=RemoveSpecialChars(A2)

Remove non-printable characters in Excel

Microsoft Excel has a special function to delete nonprinting characters - the CLEAN function. Technically, it strips off the first 32 characters in the 7-bit ASCII set (codes 0 through 31).

For example, to delete nonprintable characters from A2, here's the formula to use:

=CLEAN(A2)

This will eliminate non-printing characters, but spaces before/after text and between words will remain.

To get rid of extra spaces, wrap the CLEAN formula in the TRIM function:

=TRIM(CLEAN(A2))

Now, all leading and trailing spaces are removed, while in-between spaces are reduced to a single space character:

If you'd like to delete absolutely all spaces inside a string, then additionally substitute the space character (code number 32) with an empty string:

=TRIM(CLEAN((SUBSTITUTE(A2, CHAR(32), ""))))

Some spaces or other invisible characters still remain in your worksheet? That means those characters have different values in the Unicode character set.

For instance, the character code of a non-breaking space (&nbsp;) is 160 and you can purge it using this formula:

=SUBSTITUTE(A2, CHAR(160)," ")

To erase a specific non-printing character, you need to find its code value first. The detailed instructions and formula examples are here: How to remove a specific non-printing character.

Delete special characters with Ultimate Suite

Supports Excel for Microsoft 365, Excel 2019 - 2010

In this last example, let me show you the easiest way to remove special characters in Excel. With the Ultimate Suite installed, this is what you need to do:

1. On the Ablebits Data tab, in the Text group, click Remove > Remove Characters.
   
2. On the add-in's pane, pick the source range, select Remove character sets and choose the desired option from the dropdown list (Symbols & punctuation marks in this example).
   
3. Hit the Remove button.

In a moment, you will get a perfect result:

If something goes wrong, don't worry - a backup copy of your worksheet will be created automatically as the Back up this worksheet box is selected by default.

Curious to try our Remove tool? A link to the evaluation version is right below. I thank you for reading and hope to see you on our blog next week!

Available downloads

Delete special characters - examples (.xlsm file)
Ultimate Suite - trial version (.exe file)

You may also be interested in

• Remove text or numbers from string
• Delete first or last character in a cell
• Excel RegEx to remove characters or text from strings
• How to add text or character to multiple cells at once