Remove multiple special characters excel

Many times the database contains some special characters which we do not need in the database and we want to remove them. We can easily perform this task with the help of Excel tools and formulas. The article will explain 4 different ways that will show how to remove special characters in Excel.

Download Practice Workbook

For practice, you can download the practice book from the link below.

4 Methods for How to Remove Special Characters in Excel

We will use the following dataset to explain the ways.

The dataset contains the Names and Mail Addresses of clients of a company. You can notice Cell B8 contains a formula and it shows a non-printable value along with the name of the client "Rachel". Again, we can see there are some special characters along with all the data. We will see how to remove these special characters in Excel by using the following ways.

1. Removing Special Characters in Excel Using Excel Formulas

Excel has useful formulas which you can use to remove special characters in Excel. They are formed using the functions like SUBSTITUTE, RIGHT, LEFT, CLEAN, TRIM and REPLACE. We will look into each of them one by one.

a. Using the SUBSTITUTE Function

Let us start with the SUBSTITUTE function. It is used to replace a character with another.

Suppose you want to remove special characters from cell B5 of the given dataset.

The formula to remove specific characters using SUBSTITUTE will be:

=SUBSTITUTE(B5,"!#$$","")

Here you can notice that the specific characters mentioned in the cell are removed. It works sequentially. Hence, the character "#" remains at the beginning.

Again, you can remove recursive characters using instance numbers.

The formula will be:

Observe that the sequentially second "#" has been removed while the first one is intact.

However, you might want to remove all the characters keeping the name only.

This time the formula will be nested SUBSTITUTE within itself. The formula will look like:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B5,"#",""),"!",""),"$","")

This shows the perfect result for this case.

Formula Description:

The syntax of the formula:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

text=the text which you want to work with.

old_text= text which you want to remove.

new_text= replaced text. ( For our case we replace it with blank “ “).

instance_name= the number of the special character in case of recursive characters present in the text.

Special Case:

The special characters contains code numbers and we can get their code number using the formula:

or

The RIGHT or LEFT function is used to get the location of the character whose code you want to get.

Therefore this process includes two steps:

• Getting Code using the formula of CODE nested with RIGHT or LEFT.
• Using SUBSTITUTE formula and in place of old_text write CHAR(number).

For the result, serially follow the pictures below along with formulas.

=SUBSTITUTE(C5,CHAR(109),"")

=SUBSTITUTE(C5,CHAR(77),"")

Moreover, if similar characters are found this process will remove both. Observe the results below.

=SUBSTITUTE(B7,CHAR(42),"")

=SUBSTITUTE(B7,CHAR(94),"")

b. Using RIGHT or LEFT Functions

Considering, you have already seen in the above way the use of RIGHT and LEFT functions. These can be used with the LEN function to remove specific characters in Excel.

The formula will be:

You can increase the values to any number and subtract it with LEN(text) to remove a specific amount of special characters.

For this the formula is:

Similarly for LEFT formula,

And for increment of instance number, the changed formula:

Formula Description:

The syntax of the formula:

=RIGHT(text, [num_chars])

text= the text from where you want to remove characters.

num_chars= number of characters to be removed.

text= the text whose length you want to count.

-1 or -(any number) is the number of characters you want to subtract from the total number of characters in a text.

c. Using CLEAN and TRIM Functions

Your dataset might contain non-printable characters and extra space as well. CLEAN and TRIM functions can be used to remove them.

The formula for removing non-printable character is:

To remove non-printable characters along with extra spaces you can use the formula:

Nevertheless, you can do both by nesting TRIM and CLEAN with SUBSTITUTE. The formula will look like:

=TRIM(CLEAN(SUBSTITUTE(B8,CHAR(4),"")))

Follow the picture below.

Formula Description:

The syntax of the individual formula:

Here, text= the text from where you want to remove the non-printable character.

text= the text from where the extra space needs to be removed.

=SUBSTITUTE(text, old_text, new_text, [instance_num])

text=the text which you want to work with.

old_text= text which you want to remove.

new_text= replaced text. ( For our case we replace it with blank “ “).

instance_name= the number of the special character in case of recursive characters present in the text.

d. Using the REPLACE Function

Further, there is another formula using the REPLACE function to remove a specific amount of characters after a number of characters.

The formula is:

=REPLACE(old_text, start_num, num_chars, new_text)

Here the formula is pretty much similar to SUBSTITUTE. It takes 2 more arguments named start_num ( the number from which the characters need to be removed).

num_chars ( the number of characters to be removed).

And it does not take text as an argument which is needed for SUBSTITUTE.

The formula for the given dataset is to remove special characters after “#Sen“.

Read more: How to Remove Specific Characters in Excel

2. Use of Flash Fill to Remove Special Characters in Excel

Moving on with Excel tools. The Flash Fill is the easiest way to remove special characters in Excel.

Let us say we have names and mail addresses of clients in the same column and those are separated by a comma. We want to remove the texts after the comma including the comma. Follow the steps to know how to use the Flash Fill to remove special characters in Excel.

• Write the first text without special characters.
• Start writing the second text and you will notice Excel is showing suggested texts. Observe the picture below.

• Press ENTER from the keyboard. This will show the result as below.

Read more: How to Remove Blank Characters in Excel

3. Use of the Find & Replace Command to Remove Special Characters

Another useful tool of Excel is Find & Replace.

Suppose we want to remove “Mailto:” before the address in the column named Mail Address of the dataset.

Follow the steps below to remove special characters using Find & Replace.

• Select Replace from Find & Replace. Follow the picture below to get Find & Replace from the Editing options of the Home tab.

• A dialog box will open up. Write the character you want to remove in the Find what: box and keep the Replace with: box blank. See the picture below.

• Click Replace All and a new box will open. It will show the number of replacements done.
• Click OK.

You will see the result as follows.

Read more: How to Remove Spaces in Excel: With Formula, VBA & Power Query

4. Removing Special Characters Using the Power Query Tool

Certainly, if you are using Microsoft Excel 2016 or Excel 365 then you can use Power Query to remove special characters in Excel.

In case, you are using Microsoft Excel 2010 or 2013, you can install it from the Microsoft website.

You can follow the steps to use Power Query to remove special characters from your dataset.

• Select your range of data along with the header.
• Then choose From Table/Range from the Data tab.

• You will find a small box. Check the range of your selected data and tick My table has headers option.
• After that, click OK.

A new window named Power Query Window will open.

• Select Custom Column from the Add Column tab in the Power Query window.

• It will open the Custom Column box.
• Write “Without Special Characters” in the New column name option. You can write any name you want.
• Then, write the formula below in the Custom column formula option.

Formula:

=Text.Select([NAME],{"A".."z","0".."9"})

• Afterward, click OK.

A new column will be created and your new formula will be shown in the formula bar of the window.

• Choose Close & Load from the File tab of the window.

You will find a new worksheet in your workbook where you will see the final result as shown here.

You can notice this process did not remove the “^^” characters from cell D7. It is because Excel considers the character within the category of “. .” character.

Things to Remember

Unfortunately, if you are using Microsoft Excel versions older than 2010, you might not be able to install Power Query. You can use this feature only with versions 2010 to the latest.

Anyway, all the methods have pros and cons so use them wisely according to your requirement.

Conclusion

The article described 4 different ways to remove special characters in Excel. We have used Excel formulas and tools to explain the 4 different ways of removing special characters in Excel. to do this. In short, the formulas include functions like SUBSTITUTE, CLEAN, RIGHT, CODE, and so on. On the other hand, the tools used are Flash Fill, Find & Replace and Power Query. I hope this article was helpful to you. For any further queries, write in the comment section.

Related Articles

• How to Remove Characters in Excel (6 Methods)
• Remove First Character from String in Excel (6 Quick Ways)
• How to Remove Last Character in Excel(Easiest 6 Ways)
• How to Remove Characters from Left in Excel (6 Ways)
• Excel Remove Characters From Right (5 ways)
• How to Remove Non-numeric Characters from Cells in Excel
• How to Remove Space In Excel Before Numbers (3 Ways)