In this article, you will learn how to delete specific characters from a text string and remove unwanted characters from multiple cells at once. Show
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 Excel cellTo 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:
 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:
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:
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:
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 stringIn situation when there are two or more unwanted characters in a cell, you can nest several SUBSTITUTE functions one into another to eliminate them all 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:
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 "¿":
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 onceThe 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:
To be able to use this function in your worksheets, you need to name it first. For this, press Ctrl + 3 to open the Name Manager, and then define a New Name in this way:
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:
For convenience, we input unwanted characters in some cell, say D2. To remove those characters from A2, the formula is:
For the formula to work correctly, please take notice of the following things:
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:
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 setTo 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:
To remove numbers from text strings, we've created one more function named RemoveNumbers:
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:
To delete only 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 VBAThe 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:
Custom function with hardcoded charactersIf 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 = "?¿!¡*%#$(){}[]^&/\~+-" 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:
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:
Remove non-printable characters in ExcelMicrosoft 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. 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: 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:
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 ( ) is 160 and you can purge it using this formula:
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 SuiteSupports 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:
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 downloadsDelete special characters - examples (.xlsm file) You may also be interested in |
