Table of Contents
To remove special characters in Google Sheets, you can use the substitute function to replace them with an empty string or other character. You can also use the regexreplace function to remove special characters that match a certain pattern. Additionally, you can use the filter function to remove the rows and columns that contain the special characters. Finally, you can also use the find and replace option to search for and replace the special characters with nothing.
You can use the following formulas to remove special characters in Google Sheets:
Method 1: Remove Everything Except Letters
=REGEXREPLACE(A1,"[^A-Za-z]+","")
Method 2: Remove Everything Except Letters & Numbers
=REGEXREPLACE(A1, "[^0-9a-zA-Z]", "")
Method 3: Remove Specific Special Characters
=REGEXREPLACE(A1, "[!$%]", "")
The following examples show how to use each formula in practice.
Example 1: Remove Everything Except Letters
We can use the following formula to remove everything except letters from a cell:
=REGEXREPLACE(A2,"[^A-Za-z]+","")
The following screenshot shows how to use this formula in practice:
Notice that every special character and number has been removed from the original values.
All that we’re left with are the letters from each cell.
Example 2: Remove Everything Except Letters & Numbers
We can use the following formula to remove everything except letters and numbers from a cell:
=REGEXREPLACE(A2, "[^0-9a-zA-Z]", "")
The following screenshot shows how to use this formula in practice:
Notice that every special character has been removed from the original values.
All that we’re left with are the letters and numbers from each cell.
Example 3: Remove Specific Special Characters
We can use the following formula to remove specific special characters from a cell:
=REGEXREPLACE(A2, "[!$%]", "")
This particular formula removes the following special characters from a cell:
- !
- $
- %
Note that you can add as many specific special characters as you’d like between the brackets in the REGEXREPLACE() formula.
The following screenshot shows how to use this formula in practice:
Notice that every exclamation point (!), dollar sign ($), and percentage sign (%) has been removed from the original cell.
All other values from the original cell are kept.