How to Remove Characters in Excel (6 Methods)

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

Overview

In the following image, you will find an overview of the whole article.

Download Practice Workbook

You can download the free practice Excel workbook 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 Find and Replace Feature

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.

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

• 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 SUBSTITUTE Function

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,

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,

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

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

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

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,

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

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

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 Nested SUBSTITUTE Function

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,

Where,

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

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,

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

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 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,

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.

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

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 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,

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

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

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

• How to Remove Last Character in Excel(Easiest 6 Ways)
• How to Remove the First Character from a String in Excel with VBA
• Delete Blank Columns in Excel (3 Ways)
• How to Delete Every Other Row In Excel (4 Methods)
• Remove Text After Character in Excel (3 Ways)
• How to Delete Multiple Rows in Excel (3 Methods)