How to Remove Characters in Excel (6 Methods)

While working with a large dataset, it is not a wise way to remove characters manually when needed. In this article, we will show you how to remove characters in Excel in six different methods.


Download Practice Template

You can download the free practice Excel template from here and practice on your own.


6 Methods to Remove Characters in Excel

This section will explain six methods to remove characters in Excel by using Excel features and functions and formulas.

1. Remove Specific Characters with the Find and Replace Feature in Excel

The Find & Replace command is the easiest and the most common feature to do most of the Excel-related tasks. Here we will get to know how to delete characters by using the Find & Replace feature in Excel.

Below is the dataset consisting of Name and their Profession, where the Profession is totally messed up with unnecessary dots (.). So by using the Find & Replace feature, we are going to delete all those irrelevant dots (.) and get the desired output.

The steps to do that are given below,

Steps:

  • Select the dataset.
  • Under the Home tab, go to Find & Select -> Replace.

find & replace to remove characters characters in excel

  • From the pop-up Find and Replace box, in the Find what field, write the dot (.)
  • Leave the Replace with field blank.

replace dot with blank to remove characters in excel

 

  • Press Replace All.

This will erase all the dots (.) from your dataset in Excel.

Read more: How to Remove Characters from Left in Excel


2. Delete Specific Characters with the SUBSTITUTE Function in Excel

Unlike Find & Replace command feature in Excel, using formula is the safest and the most controlled way to extract any kind of results in Excel. To get the output of a dataset without any specific character in Excel, you can implement the SUBSTITUTE function.

Generic SUBSTITUTE Formula,

=SUBSTITUTE(cell, “old_text”, “new_text”)
Here,
old_text = the text you want to remove
new_text = the text that you want to replace with

Below is the same dataset that we used in the above section where the Wrestlers’ Profession is totally messed up with unnecessary dots (.). And this time, instead of using the Find & Replace feature to remove a character, we are going to apply the SUBSTITUTE function to get the desired output.

Steps:
Steps to remove dashes in Excel with the SUBSTITUTE function are given below,

  • In an empty cell where you want your result to appear, first put an equal (=) sign and then write SUBSTITUTE along with it.
  • Inside the brackets of the SUBSTITUTE function, first, write the cell reference number from which you want to remove dot (.) (in our case, the Cell number was C5).
  • Then put a comma (,) symbol and after that, write dot (.) symbol inside double quotes (or any old text that you want to remove).
  • Again put a comma (,) and lastly, leave a blank double quote if you want a null string instead of a dot (.) (or any new string that you want your old text to replace with).

So, our required formula will look like the following,

=SUBSTITUTE(C5,”.”,””)
  • Press Enter.

substitute function to remove characters in excel

It will replace all dots (.) (or any other text that you selected) with a null string (or the string that you replace it with).

  • Drag the row down using Fill Handle to apply the formula to the rest of the dataset.

dragging row to remove characters in excel

Now you have found the result of a dataset without any dots (.).

Read more: Excel Remove Characters From Right


3. Extract only a Particular Instance of a Specific Character in a String in Excel

Till now we were only learning how to remove specific characters from an Excel dataset and we were deleting all of those characters. But what if, you want to delete that specific character only from a certain position.

Such as, instead of removing all the dots (.) from the Wrestlers’ Profession of the below dataset, we want to delete only the 1st dot (.) (or any other position number of the dot) from each cell.

Let’s see how to do that.

Steps:

  • Just like the above section where we implemented the SUBSTITUTE function to remove dots (.), here we will just define the certain position from which we want to remove the dot (.).

So the above SUBSTITUTE formula,

=SUBSTITUTE(C5,”.”,””)
Becomes,
=SUBSTITUTE(C5,”.”,””,1)

Here, 1 means, we want to remove the 1st dot (.) from our dataset (if you want to remove the 2nd character from your dataset then just write 2 instead of 1, if you want to remove the 3rd character from your dataset then just write 3 instead of 1, and so on).

substitute to remove specific characters in excel

  • Again, just drag the row down using Fill Handle to apply the formula to the rest of the dataset.

dragging row to remove characters in excel

Now you have found the result of a dataset without the 1st dot (.).

Read more: How to Remove First Character in Excel


4. Remove Multiple Specific Characters with the Nested SUBSTITUTE Function in Excel

Have you noticed in the previous section that when we were removing a dot (.) from a specific position, the cells that were filled with multiple dots (.) were not fully affected? That’s because the SUBSTITUTE function only removes one character for any number of instances at a time. So, if you want to remove multiple unwanted characters at once then you need to implement the nested SUBSTITUTE function.

Look at the dataset below where the Profession cells beside the names Finn and Roman consist of multiple dots (.).

So let’s find out how to implement the nested SUBSTITUTE function to remove multiple characters at once.

Steps:

  • To establish a nested SUBSTITUTE function, you have to write a SUBSTITUTE function inside another SUBSTITUTE function and pass relevant arguments inside the brackets.

To understand more, check the picture below,

nested substitute to remove characters in excel

Where,

To remove multiple dots (.) from the C5 Cell, first we write the formula,

=SUBSTITUTE(C5,”.”,””)

and then, to delete another dot(.) (or any other character that you required) along with it, we put this formula inside another SUBSTITUTE formula and pass the arguments (old_text, new_text) inside it (in our case, it was “.”,””).

So, now the formula is,

=SUBSTITUTE(SUBSTITUTE(C5,".",""),".","")
  • Press Enter.

It will replace all dots (.) (or any other text that you selected) with a null string (or the string that you replace it with).

  • Once again drag the row down using Fill Handle to apply the formula to the rest of the dataset.

dragging row to remove characters in excel

Now you have found the result of a dataset without any dots (.).

Read more: How to Remove Special Characters in Excel


5. Erase any Special Characters with the CLEAN Function

Suppose you copied a huge dataset from somewhere on the internet and pasted it in your Excel sheet. So naturally, the sheet is now ended up with a lot of unnecessary characters like new-line characters, spaces etc. To remove those needless data, you can implement the CLEAN function in Excel.

Excel CLEAN function removes line breaks and non-printable characters from a string. The general syntax for this function is,

=CLEAN(original_string)

Here,

original_string = the text or reference to the text cell that you want to clean.

Let’s take a look at the following data table, where we copy and pasted the Harry Potter Movie Series in our Excel sheet. So, this dataset is a wreck right now. Let’s find out how to clean the mess that we just made.

Let’s see how to use the CLEAN function to clean this data.

Steps:

  • Select the cell of the column where you want the results to appear. (In our example, it was Cell D5).
  • Type the formula:
    =CLEAN(B5)
  • Press Enter.

clean function to remove characters in excel

  • This will give you the text obtained after removing all line breaks from the string in Cell B5.
  • Now drag the row down using Fill Handle to apply the formula to the rest of the dataset.

dragging row to remove characters in excel

And by doing that, now you can have your clean dataset without any line breaks and unnecessary data.

Read more: How to Remove Blank Characters in Excel


6. Eliminate Space Characters with the TRIM Function

The TRIM function removes the space character (“ “) from any string in Excel. It removes all the leading or trailing spaces in texts. It also removes extra spaces between words and leaves just a single space.

The general syntax for this function is,

=TRIM(original_string)

Here,

original_string = the text or reference to the text cell that you want to process.

Look at the following dataset with so many useless spaces.

Let’s learn how to remove the unnecessary space characters from a dataset in Excel.

Steps:

  • Select the first cell of the column where you want the results to appear. (In our example, it was Cell D5).
  • Type the formula:
    =TRIM(C5)
  • Press Enter.

trim function to remove characters in excel

  • This will give you the text obtained after removing all unnecessary spaces from the string in Cell C5.
  • Now drag the row down using Fill Handle to apply the formula to the rest of the dataset.

dragging row to remove characters in excel

And after that, you will get your dataset without any unnecessary space characters.

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


Keep in Mind

There is a catch of using the CLEAN function. There are many non-printable characters in Unicode that CLEAN cannot remove. The CLEAN function only removes the first 32 (non-printable) characters in the 7-bit ASCII code (i.e., values 0 to 31). So, it is best to apply the TRIM function after applying the CLEAN function to remove the spaces, since the space character has a value of 32, as a result, the CLEAN function cannot remove space characters.


Conclusion

This article explained six methods to remove characters in Excel by using Excel features, and functions and formulas. I hope this article has been very beneficial to you. Feel free to ask any questions if you have regarding the topic.


You May Also Like to Explore

Sanjida Ahmed

Sanjida Ahmed

Hello World! This is Sanjida, an Engineer who is passionate about researching real-world problems and inventing solutions that haven’t been discovered yet. Here, I try to deliver the results with explanations of Excel-related problems, where most of my interpretations will be provided to you in the form of Visual Basic for Applications (VBA) programming language. Being a programmer and a constant solution seeker, made me interested in assisting the world with top-notch innovations and evaluations of data analysis.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo