How to remove special (unwanted) characters from string in Excel

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.

Note. If you noticed an unexpected sequence of number characters (#) in your dataset, you can learn about possible reasons and solutions here: How to remove #### error from Excel cell.

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, "?", "")
Formula to remove a special character from a cell

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, "¿", "")
Remove a special character that is not present on your keyboard

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))
Finding the code number of a special character

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),"")
Remove a special character by its code value

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), "")
Delete multiple characters by using nested SUBSTITUTE functions

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.
Creating a custom LAMBDA function to remove unwanted characters

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:
Removing unwanted characters with a custom function

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:
Dynamic array formula to remove unwanted characters

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:

=RemoveSpecialChars(A2)
A custom function to remove special characters

To delete only numeric characters:

=RemoveNumbers(A2)
A custom function to remove numeric characters

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.

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 If End 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 = str End 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)
A custom VBA function to remove unwanted characters in Excel

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 = str End 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:

RemoveSpecialChars(string)

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

=RemoveSpecialChars(A2)
A custom VBA function to delete predefined characters

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.
Formula to delete non-printable characters

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:
Formula to remove non-printable characters and trim extra spaces

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), ""))))
Formula to strip non-printable characters and all spaces

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.
    Remove characters in Excel
  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).
    Removing symbols & punctuation marks
  3. Hit the Remove button.

In a moment, you will get a perfect result:
Special symbols and punctuation marks are removed.

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)

Table of contents

29 comments

  1. Hi, I need to remove japanese characters from english letters

  2. I have the function saved in my personal.xlb and call it with =Personal.xlsb!RemoveSpecialChars(Column to fix)

    My question is how do I add double quotes to the list of characters to remove? I want to get rid of every special character

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

    • Hi!
      To add quotes to unwanted characters, in the code of the RemoveSpecialChars function, change the chars variable

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

  3. Please l need help, substitute formula didn't work.

    how to simply remove # in a roll of number or amount
    e.g #2,000
    #5,000
    #4,500

    Thank you.

  4. HI Sir,

    Is there a formula to remove the commas and any special characters

    Example: PIPE,,,3/4" X 6 meters,,SCH. 80,,,

    To become: PIPE,3/4" X 6 meters,SCH. 80

    Thank you.

  5. Hello,
    Is there a formula to remove text and any special characters (leaving just numbers).
    For example I want:
    0.1819515.004 - Example Text 
    to become:
    01819515004

    I am currently using substitute and left search but cant get exactly what I need.

    Many thanks

  6. Hi. Can you help me how to eliminate the duplicate special characters (commas) of the items below.

    Samples Should be
    1. BEARING,,6310-2ZC3,, 1. BEARING,6310-2ZC3
    2. ELBOW,,,4", 2. ELBOW,4"
    3. PIPE,,,3/4" X 6 meters,,SCH. 80, 3. PIPE,3/4" X 6 meters,SCH. 80
    4. BOLT,,,STUD,,,M8X3m,,, 4. BOLT,STUD,M8X3m

      • Sir I am sorry. That is not what I meant. I just it posted it wrong because I include what it should turn out. All I just want is to remove all the commas. for example,

        BEARING,,,BALL,,6310-2ZC3,,. I just want to be in this format. BEARING,6310-2ZC3

        • Hi!
          Pay attention to the following paragraph of the article above: Delete multiple characters from string.
          If you do not want to delete the first comma, replace it with another character using the SUBSTITUTE function. For example,
          =SUBSTITUTE(A1,",","#",1)
          Remove all commas, as described in the article above, and then do a reverse substitution of that character for a comma.
          =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,",","#",1),",",""),"#",",")
          Hope this is what you need.

  7. Hi,

    I am working on imported data and want to separate additional numbers from a date string. I was able to remove the text from the cell, but there are additional number identifiers that I need to remove to just have the date in the cell. The original cell data example from the import is: INC-2022-08-01-1107 Railroad Street. I was able to remove "INC-" and "Railroad Street" from the cell, which left me with "2022-08-01-1107" and I need to remove the "--1107" from the cell. I can use the LEN function, but I have over 2000 cells to process and the LEN is often different in each cell.

    Thank you for any insight or advice.

    Jon

  8. There is a typo in "For this, press Ctrl + 3 to open the Name Manager". It should be "Ctrl + F3".

  9. Hi there, I am trying to cleanse some address data. For example "1713 - 1715 St. Patrick St. Apt. #145 & 146". I am trying to get this into the format "1713 St Patrick St Apt 145". Any advice would be much appreciated. Thanks

  10. Good day

    I am having a excel spread sheet that keeps saying "Not Responding" I am seeing a strange blue, yellow and red character in the corner of my cells. I think the text could have been copied to the workbook as a picture via special past, but I do not how to undo it or clear the character out of the cell?

  11. Thanks, very useful examples

  12. is there anyway to remove one special characters only in a cell.
    for example your data is 2.632.91738, here's my formula and it works =LEFT(A1, SEARCH(".",A1)-1) & RIGHT(A1,LEN(A1) - SEARCH(".", A1)).

    however, if my data is 2632.91737 using same formula it deletes the ".".
    all I need to do is if the data have 2 period "." delete the first one, and if the data has a one "." period only, this period remains.

    Please help..thanks

    • Hello!
      To remove only the first point in the number, if there are two such points, use the formula

      =IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>1,SUBSTITUTE(A1,".","",1),A1)

  13. I would like to know more various examples and details about the recursive usage of LAMBDA function.

  14. I want to learn VLOOKup and Getdata function in Excel.

    If someone help me on this.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)