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
“” : empty string
Let’s use this function on some of the uncleaned values shown below.
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
Explanation:
This formula extracts every single space in the cell value and replaces it with an empty string.
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
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.
Use the formula to remove the character
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
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.
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:
How to Remove leading and trailing spaces from text in Excel
How to use the RIGHT function in Excel
How to Remove unwanted characters in Excel
How to Extract Text From A String In Excel Using Excel’s LEFT And RIGHT Function
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity
How to use the VLOOKUP Function in Excel
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.
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?
To split numbers and text from a string use, I have written a different article. Here is your solution.
https://www.exceltip.com/excel-text-formulas/split-numbers-and-text-from-string-in-excel.html