There are a few ways you can remove characters from cells in Excel. You can do it from the ribbon, using the formula, or the VBA code. You can get rid of a single, or multiple characters.
Remove characters with Find and Replace
The Find and Replace feature is the most straightforward way to replace the character. You can also use it to remove a character by replacing a searched character with a space. Here’s how it works:
This example shows a list of names separated by commas:
To remove commas, select values inside the table and navigate to Home >> Editing >> Find & Select >> Replace.
You can also use the Ctrl + H keyboard shortcut to access this feature.
In Find what insert a comma, and in Replace with don’t enter anything.
Click Replace All to replace all commas to empty spaces. In other words, remove commas.
Remove characters using a formula
Another way to remove characters is to use the SUBSTITUTE function. This function takes a string and removes one type of character with a different type.
To use this formula, you have to create a second column in your table:
|
1 |
=SUBSTITUTE(A2,",","") |
This function takes a text as the first parameter. In the second parameter, we specify a string (in our case a single character that is a comma) that Excel searches for. This character is replaced with the character in the third argument. In our case, it’s a space.
