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 SUBSTITIUTE 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
Lets 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.
Just use the char(code) in place of remove_char. To know the code of the character use the function shown below.
4
Use the formula to remove 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 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.

Hope you understood how to remove unwanted characters fromt he 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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

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