How to Remove a Specific Character from a String in Excel

A big part of processing data involves the cleaning of the data.

Most data you bring in to Excel often comes from various sources. Many times they are converted from a different format. As such, it is not uncommon to find unwanted characters inside text string data.

What’s more annoying is that sometimes these unwanted characters are invisible. So they fail to give expected results when operations are performed on them. Another common problem is the presence of inconsistent blank spaces.

In this tutorial, we will address all the above issues using Excel functions, formulas, and features. We will see how to use various Excel functionalities to remove specific characters from strings in your cells.

Table of Contents

Removing a Specific Character with the Find and Replace Feature

Excel’s Find and Replace dialog box is a great way to find items on your worksheet and get things done quickly.

All you need to do is enter your search string to specify what you want to replace and then specify what you want to replace it with.

Suppose you have the below dataset and you want to remove all the ‘@’ characters from the text string in each cell.

Below are the steps to remove a specific character using Find and Replace:

Select the range of cells you want to work with.
Click on Find & Select from the Home tab (under the ‘Editing’ group).
This will display a dropdown menu. Select ‘Replace’.
This will open the Find and Replace dialog box. Type ‘@’ in the text box next to ‘Find what’.
Leave the text box next to ‘Replace with’ blank. This is because you want to remove any instance of the ‘@’ symbol in each cell.
Click on the ‘Replace All’ button.

This will remove all instances of the ‘@’ symbol from all the cells.

Also read: Find the Position of a Character in a String in Excel

Removing a Specific Character with the SUBSTITUTE Function

The SUBSTITUTE function can be used to remove a specific character from a string or replace it with something else. The general syntax for this function is:

=SUBSTITUTE (original_string, old_character, new_character, instance_number)

Here,

original_string is the text or reference to the cell that you want to work on.
old_character is the character that you want to replace.
new_character is the character that you want to replace old_character with.
instance_number is optional. It is the instance of the old_character that you want to replace.

It is possible to customize the above formula to the make it suitable to remove a specific character from a string, as follows:

=SUBSTITUTE (original_string, old_character, “”)

This formula will replace the old_character with a blank (“”), which means the character will basically get deleted.

Let us assume you have the same set of string values with the ‘@’ symbol in random places, and you want to remove all of them:

For this, you can use the SUBSTITUTE function with the following steps:

Select the first cell of the column where you want the results to appear. In our example, it will be cell B2.
Type the formula:
```
=SUBSTITUTE(A2,"@","")
```
Press the return key.
This will give you the text obtained after removing all instances of the ‘@’ symbol in cell A2.
Double click the fill handle (located at the bottom-left corner) of cell B2. This will copy the formula to all the other cells of column B. You can also choose to drag down the fill handle to achieve the same effect. Here are both the original and converted columns side by side:
If you want to retain only the converted versions of the text, then select these cells (B2:B5), copy them, and paste them in the same place as values.
You can then delete column A if you need to.

Also Read: How to Remove the Last Digit in Excel?

Removing only a Particular Instance of a Specific Character in a String

Now, what if you wanted to remove just the first ‘@’ symbol from each cell, instead of all instances of them?

This is where the last optional parameter of the SUBSTITUTE function comes in handy.

Using this, you can specify which instance of the symbol you want to remove. So, to remove the first instance of a symbol, your function should be:

SUBSTITUTE (original_string, old_character, “”,1)

Similarly, if you want to remove the second instance of the character, the function will be:

SUBSTITUTE (original_string, old_character, “”,2)

Let’s see the steps to remove only the first instance of the ‘@’ symbol from the above dataset:

Select the first cell of the column where you want the results to appear. In our example, it will be cell B2.
Type the formula:
```
=SUBSTITUTE(A2,"@","",1)
```
Press the return key.
This will give you the text obtained after removing only the first ‘@’ symbol in cell A2.
Double click the fill handle (located at the bottom-left corner) of cell B2. This will copy the formula to all the other cells of column B. You can also choose to drag down the fill handle to achieve the same effect. Here are both the original and converted columns side by side:
If you want to retain only the converted versions of the text, then select these cells (B2:B5), copy them, and paste them in the same place as values.
You can then delete column A if you need to.

Also read: How to Remove First Character in Excel?

Removing any Special Character with the CLEAN Function

The 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 is the text or reference to the text cell that you want to clean.

The result is the text that has all non-printable characters removed.

Let’s take a look at the following set of strings.

Since the list was brought in from another application, it ended up having a lot of unnecessary characters, like new-line characters, spaces, etc.

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

Select the first cell of the column where you want the results to appear. In our example, it will be cell B2.
Type the formula:
```
=CLEAN(A2).
```
Press the return key.
This will give you the text obtained after removing all line breaks from the string in cell A2.
Double click the fill handle (located at the bottom-left corner) of cell B2. This will copy the formula to all the other cells of column B.

Notice that the new line characters got removed, but the results still don’t look right. This is because when the data was brought in, it also contained some space characters, besides the new lines.

The CLEAN function removes only the first 32 (non-printable) characters in the 7-bit ASCII code (i.e. values 0 to 31). However, there are other non-printable characters in Unicode that CLEAN cannot remove.

Since the space character has a value of 32, the CLEAN function does not remove spaces. So it is best to apply the TRIM function after applying the CLEAN function to remove the spaces.

Also read: How To Remove Formulas In Excel But Keep Data

Removing Leading or Trailing Space Characters with the TRIM Function

A lot of data cleaning merely consists of removing leading or trailing space characters from strings. Excel’s TRIM function makes this easy to do in just one go.

The TRIM function removes the space character (“ “) from the text. If the spaces are leading or trailing spaces, it removes all of them. If there are extra spaces between words, then it removes the extras and leaves just a single space.

The general syntax for this function is:

=TRIM (original_string)

Here, original_string is the text or reference to the text cell that you want to process.

Let us use the TRIM function to remove the space characters that were left over after applying the CLEAN function:

Select the first cell of the column where you want the results to appear. In our example, it will be cell C2.
Type the formula:
```
=TRIM(B2).
```
Press the return key.
This will give you the text obtained after removing all unnecessary spaces from the string in cell B2.
Double click the fill handle (located at the bottom-left corner) of cell C2. This will copy the formula to all the other cells of column C.
If you want to retain only the converted versions of the text, then select these cells (C2:C5), copy them, and paste them in the same place as values.
You can then delete columns A and B if you need to.

Also read: Remove the Last 4 Characters in Excel

Removing a Specific Invisible Character from a String using SUBSTITUTE, CHAR and CODE Functions

In some cases, both CLEAN and TRIM functions fail to remove some particularly annoying characters from the string.

This may be because these characters are neither spaces nor one of the 32 characters that the CLEAN function can remove.

You can find the code of a character by using the CODE function. For example, in the sample text below, there is an invisible character at the start of the string.

Since it couldn’t be removed with a TRIM or CLEAN, it is quite evident that it’s not a regular space.

Here’s what we can do to remove all instances of the invisible character:

To find out what this character is, we can use the CODE function. Type this function into the cell B2: =CODE(LEFT(A2)). Since the character is in the first position in the text, we can easily find out its code using the LEFT function. In this case, we get the result as “160”. That means the invisible character’s code is 160.
Let us use this value in the SUBSTITUTE function. Type this function into the cell C2: =SUBSTITUTE(A2,CHAR(B2),””). Here we used the CHAR function to convert the character code back to its character equivalent.
When you press the return key now, you will find all instances of that invisible character removed from the string.

In this tutorial, we saw how you can use various Excel functions, formulas, and features to remove specific characters from a string.

If you know what the character you want to remove is, you can use either the Find and Replace feature or the SUBSTITUTE function.

To remove blank spaces and special characters (that often accompany data brought in from other applications) you can use the CLEAN and TRIM functions respectively.

If there are other invisible characters in the string and you don’t know what the characters are, you can use a formula that combines the CODE, CHAR, and SUBSTITUTE functions together.

We tried to put together all possible situations where you would need to remove a specific character from text in Excel.

Hope you found the tutorial useful!

Other Excel tutorials you may like: