When dealing with data exported from a system or a database, we often see characters in our data that we would rather remove. Manually removing them can take a lot of time. Good thing Excel provides ways for us to easily erase unwanted characters from a string in just one go.
Table of Contents
Using the Find and Replace Function to Remove a Specific Character from a String in Excel
One of the fastest ways to remove a particular character or text from multiple cells is through the use of Excel’s Find and Replace function.
The logic behind this method is quite simple: find the character to remove and replace it with a blank value to “remove” it.
To do this:
1. Highlight the cells containing the character you want to remove.
If you want to select the entire column, click on the corresponding column letter. This is also what you’ll first do if you want to remove the duplicates on that column.
IMPORTANT: You must select a group of cells (at least two cells). Otherwise, Excel will think the search should be performed on the entire sheet (not just with the selected cell).
2. Press CTRL + H to open the Find and Replace menu.
In the Find What textbox, specify the character you want to remove. In my example above, I have added the apostrophe (‘).
Leave the Replace With textbox blank.
Once done, click the Replace All button.
3. And that’s it! You have successfully removed all instances of the specified character.
If the outcome is not what you expected it to be, press CTRL + Z to undo it.
You can then redo the steps and make the necessary adjustments to the search parameters.
PRO TIP:
If you want to remove an alphabet character or a text, remember to tick the Match case checkbox when you want to remove only the text that matches the same letter case (whether lowercase or uppercase).
Note: If you can’t find the Match case checkbox, click the Options >> button.
ANOTHER PRO TIP:
If you want to also remove the series of texts that comes before or after a particular character, add an asterisk (*) in the Find What textbox.
The asterisk serves as a wildcard character in Excel, representing any character.
So, if you want to also remove the text that comes AFTER a specified character, add an asterisk after it
Example: _*
In the example above, Excel will remove the underscore (_) along with text that comes after it. So, the output will be:
In the same way, aside from the specified character, if you want to remove the text that comes BEFORE it, just add the asterisk before that character.
Example: *_
Using the SUBSTITUTE() Function to Remove a Specific Character from a String in Excel
Another way to remove a character from a string is by using the SUBSTITUTE() function. This method is perfect if you’re looking for a way to only delete a specific instance of the character.
On a blank column, enter the following formula:
=SUBSTITUTE([range], [old text], [new text], [instance to remove])
- [range] refers to the cell containing the character you want to remove.
- [old text] refers to the character you intend to remove.
- [new text] refers to the replacement for that character. Since we want to remove it, we’ll leave this blank.
- [instance to remove] is an optional parameter. Use this to specify the nth instance you would like to remove from the string. If you want to remove the first instance, type 1. If the second instance, type 2. Leave this blank if you want to remove all instances of the character.
Remember to enclose both [old text] and [new text] in double quotes (“) to signify that they are text characters.
Example: =SUBSTITUTE(A2, “_”, “”, 1)
In my example above, I intend to remove the first instance of comma and space in cell A2.
After entering the correct formula, you should see the result appear inside the cell.
If you’re happy with it, drag the fill handler down (up to the last row) to apply the same formula to the remaining cells.
And that’s it! You have successfully created a duplicate of the original column with the unneeded character removed (like a leading apostrophe).
You can copy this column and paste it as values onto the original column (if you want to only see this format).
Using the Flash Fill to Remove a Specific Character from a String in Excel
Another pretty cool method for removing a specific character from a string is by using Excel’s Flash Fill.
Flash Fill is one of Excel’s intuitive features that allows you to easily replicate the format of a particular cell.
On a new column (just right beside the first cell containing your string), type the output that you want to have – type the same string but without the unwanted character(s).
IMPORTANT:
Make sure that you enter the expected output in a column that is immediately next to the column containing your string. Otherwise, Flash Fill won’t work.
After entering the desired output, drag the Fill Handler down (up to the last row in your dataset).
Notice that the cells will be filled with the same contents as the first cell.
Click the Fill Handler Option (the button that appears at the end of your selection) and select Flash Fill.
And that’s it! You should now have the same set of strings but without the unwanted character.
You can copy this new column and paste it onto the original one to replace it.
Conclusion
We often receive data from various sources and at times, these data are preformatted to contain unwanted characters. Good thing Excel provides ways to eliminate these characters in just a few steps. Also you can remove text before or after a specific character for your specific needs and dataset.