How to Remove Special Characters in Excel (4 Methods)

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 to explain ways to remove special characters in excel

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,"!#$$","")

Using SUBSTITUTE to replace special characters by blank

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:

=SUBSTITUTE(B5,"#","",2)

Using SUBSTITUTE to second instance of a recursive character

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,"#",""),"!",""),"$","")

Result after removing all special characters

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:

=CODE(RIGHT(text))

or

=CODE(LEFT(text))

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.

=CODE(RIGHT(C5))

Result of using CODE to find code of specific character

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

Result of using Code inside SUBSTITUTE

=CODE(LEFT(C5))

Result of using LEFT with CODE

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

Result of SUBSTITUTE for LEFT side character

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

=CODE(LEFT(B7))

Result for recursive left character code

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

Result after removing recursive character at left

=CODE(RIGHT(B7))

Code for recursive character at right

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

Result after removing recursive character at right

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:

=RIGHT(B7,LEN(B7)-1)

Result of using RIGHT and LEN for 1 character removing

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:

=RIGHT(B7,LEN(B7)-2)

Result of Using RIGHT and LEN for more than one character removing

Similarly for LEFT formula,

=LEFT(B7,LEN(B7)-1)

Removing single character using LEFT and LEN

And for increment of instance number, the changed formula:

=LEFT(B5,LEN(B5)-4)

Result of using LEFT and LEN for more than one character

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.

=LEN(text)

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:

=CLEAN(B8)

Using CLEAN to remove non-printable character

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

=TRIM(CLEAN(B8)

Result of using TRIM and CLEAN

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.

Result of using nested TRIM, CLEAN and SUBSTITUTE

Formula Description:

The syntax of the individual formula:

=CLEAN(text)

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

=TRIM(text)

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“.

=REPLACE(B5,5,4,"")

Using REPLACE function

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.

Flash fill suggestions

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

Result of Flash Fill after entering

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.

Finding Find & Replace from 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.

Writing characters to find and replace them

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

Replacement numbers shown in result

You will see the result as follows.

Result of using Find & Replace

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.

Finding 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.

Checking table range and header

A new window named Power Query Window will open.

Table in Power Query Window

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

Finding Custom column from Add Column tab

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

Adding formula to new column

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

New column added in Power Query Window

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

Choosing close & load from file tab

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

Final result of using Power Query in new sheet

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

Syeda Fahima Nazreen

Hello People! This is Syeda Fahima Nazreen. I have completed my Bachelors in Science in Electrical and Electronic Engineering. I love to do research and work anything related to technology which includes research and development. I feel great to share my knowledge with you people and your thoughts and opinions about my writing is highly appreciated by me.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo