Remove unwanted characters in Excel

In this article, we will learn how to remove unwanted characters in Excel.

Sometimes you get uncleaned data set in excel & I don’t want you being banging your head on wall to clean the data set.

In simple words, Excel lets you clean unwanted characters using SUBSTITUTE function .
Syntax to clean unwanted characters

=SUBSTITUTE ( Text , "remove_char", "")

“” : empty string

Let’s use this function on some of the uncleaned values shown below.
1
Let's understand this one by one:
1st case:
When you need to remove just the spaces from the data set. Use the single space as remove_char in the formula

Formula

=SUBSTITUTE(A2," ","")

Explanation:
This formula extracts every single space in the cell value and replaces it with an empty string.
2
As you can see the first value is cleaned.

Second Case:
When you know a specific character to remove from the cell value, just use that character as remove_char in the formula
Use the formula

=SUBSTITUTE(A3,"!","")

3
As you can see the value is cleaned.

Third Case:
When you wish to remove the character by using its code. This can help you in removing case sensitive character.
Just use the char(code) in place of remove_char. To know the code of the character uses the function shown below.
4
Use the formula to remove the character

=SUBSTITUTE(A4,CHAR(38),"")

5
As you can see the value is cleaned.

Final Case:
When you wish to remove the character which comes at the first position in the text. You need to grab the code of the character using the LEFT & CODE function.
Use the formula

=SUBSTITUTE(A5,CHAR(CODE(LEFT(A5))),"")

Explanation:
LEFT(A5) grabs the single space code in the formula using LEFT & CODE function and giving as input to char function to replace it with an empty string.
6

7
As you can see the value is cleaned in both the cases whether it is single space or any other character.

I hope you understood how to remove unwanted characters from the text using SUBSTITUTE function in Excel. Explore more articles on Excel TEXT function here. Please feel free to state your query or feedback for the above article.

Related Articles:

Remove leading and trailing spaces from text in Excel

How to use RIGHT function in Excel

Remove unwanted characters in Excel

Extract Text From A String In Excel Using Excel’s LEFT And RIGHT Function

Popular Articles:

The VLOOKUP Function in Excel

COUNTIF in Excel 2016

How to Use SUMIF Function in Excel

Users are saying about us...

  1. Thank you for publishing your work, we have fields that contain letters, symbols, and numbers. We wish to extract or leave just the numbers. any suggestions?

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube