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, "?", "")
How to delete special / unwanted characters in Excel (1)

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, "¿", "")
How to delete special / unwanted characters in Excel (2)

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:

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

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:

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

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, "")

(Video) How to Remove Special Characters from Text Data in Excel

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), "")
How to delete special / unwanted characters in Excel (5)

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.
How to delete special / unwanted characters in Excel (6)

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)


  • 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:

(Video) Excel - Remove Special Characters

  • 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:
How to delete special / unwanted characters in Excel (7)

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:
How to delete special / unwanted characters in Excel (8)

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 2nd 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:

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

To delete only numeric characters:

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

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

(Video) How to remove Characters from fields in 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.

Function RemoveUnwantedChars(str As String, chars As String) If ("" <> chars) Then str = Replace(str, Left(chars, 1), "") chars = Right(chars, Len(chars) - 1) RemoveUnwantedChars = RemoveUnwantedChars(str, chars) Else RemoveUnwantedChars = str End IfEnd Function

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.

Function RemoveUnwantedChars(str As String, chars As String) For index = 1 To Len(chars) str = Replace(str, Mid(chars, index, 1), "") Next RemoveUnwantedChars = strEnd 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)
How to delete special / unwanted characters in Excel (11)

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:

Function RemoveSpecialChars(str As String) As String Dim chars As String Dim index As Long chars = "?¿!¡*%#$(){}[]^&amp;/\~+-" For index = 1 To Len(chars) str = Replace(str, Mid(chars, index, 1), "") Next RemoveSpecialChars = strEnd Function

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:


(Video) #Excel #Tips | How To Remove Special Characters | 2020 | Easy Steps to Remove Unwanted Characters

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

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

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:


This will eliminate non-printing characters, but spaces before/after text and between words will remain.
How to delete special / unwanted characters in Excel (13)

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


Now, all leading and trailing spaces are removed, while in-between spaces are reduced to a single space character:
How to delete special / unwanted characters in Excel (14)

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:

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

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.
    How to delete special / unwanted characters in Excel (16)
  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).
    How to delete special / unwanted characters in Excel (17)
  3. Hit the Remove button.

In a moment, you will get a perfect result:
How to delete special / unwanted characters in Excel (18)

(Video) Excel Guide - Remove special characters | MS EXCEL | Learn MS EXCEL

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


How do I remove special characters from a value? ›

The replace() method is used to replace special characters with empty characters or null values.

How do I find hidden characters in Excel? ›

Office button --> excel options --> Advance --> under the display tick show control characters.

How do I remove unwanted characters from a string? ›

Remove Characters From a String Using the replace() Method. The String replace() method replaces a character with a new character. You can remove a character from a string by providing the character(s) to replace as the first argument and an empty string as the second argument.

How do I remove hidden special characters from text? ›

To remove hidden characters from a text file, you can use a text editor such as Notepad++. In Notepad++, go to the View menu and select Show Symbol. This will display all of the hidden characters in the text file. To remove a hidden character, simply delete it from the text file.

How do you use the clean function in Excel? ›

Using the SHIFT key, select B1 to B1000. In the example, hold “Shift” and click cell “B1000” to select cells “B1” through “B1000.” Now, type “=CLEAN(A1)” (excluding the quotes) and then press “Ctrl-Enter” to apply the CLEAN function to the entire selection and clean every data point on our list.

How do I check if a string contains special characters in Excel? ›

How can we know if a string contains any special characters? Well, we can use UDF to do so. The below formula will return TRUE if any cell contains any characters other than 1 to 0 and A to Z (in both cases). If it does not find any special characters it will return FALSE.

What is the TRIM function in Excel? ›

Description. Removes all spaces from text except for single spaces between words. Use TRIM on text that you have received from another application that may have irregular spacing. Important: The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text.

How do you clean text in a cell in Excel? ›

Using the SHIFT key, select B1 to B1000. In the example, hold “Shift” and click cell “B1000” to select cells “B1” through “B1000.” Now, type “=CLEAN(A1)” (excluding the quotes) and then press “Ctrl-Enter” to apply the CLEAN function to the entire selection and clean every data point on our list.

What is the difference between trim and clean in Excel? ›

The TRIM function corrects irregular spacing by removing all spaces from text except for single spaces between words. The nested CLEAN function in the formula removes all non-printable characters from the text.

How do I delete unwanted data in Excel? ›

Select the data and Go to Data –> Remove Duplicates. If your data has headers, ensure that the checkbox at the top right is checked. Select the Column(s) from which you want to remove duplicates and click OK.


1. Remove Unwanted Characters in MS Excel
2. How to Remove Special Characters from Numbers in Excel Quickly
(Excel Advisor)
3. Remove unwanted characters from excel cell - Excel Tips by Top2Tip Episode 1
4. Remove Unwanted Characters in Excel
(Doug H)
5. Remove Unwanted Characters in EXCEL
6. Remove Hidden CHAR(160) Spaces from Excel
Top Articles
Latest Posts
Article information

Author: Rev. Porsche Oberbrunner

Last Updated: 02/03/2023

Views: 5685

Rating: 4.2 / 5 (53 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Rev. Porsche Oberbrunner

Birthday: 1994-06-25

Address: Suite 153 582 Lubowitz Walks, Port Alfredoborough, IN 72879-2838

Phone: +128413562823324

Job: IT Strategist

Hobby: Video gaming, Basketball, Web surfing, Book restoration, Jogging, Shooting, Fishing

Introduction: My name is Rev. Porsche Oberbrunner, I am a zany, graceful, talented, witty, determined, shiny, enchanting person who loves writing and wants to share my knowledge and understanding with you.