How to Remove Special Characters in Google Sheets

To remove special characters in Google Sheets, you can use a combination of functions like SUBSTITUTE, REGEXREPLACE, or a custom function. Here's a step-by-step guide on how to remove special characters using these functions:

Using SUBSTITUTE and CHAR Functions

1. Open your Google Sheet containing the data with special characters.
2. Click on an empty cell where you want to display the cleaned data.
3. Type the following formula: =SUBSTITUTE(A1,CHAR(33),""), replacing A1 with the cell that contains the text with special characters, and 33 with the ASCII value of the special character you want to remove. For example, 33 is the ASCII value for "!". You can find the ASCII values for various special characters online.
4. Press Enter to apply the formula.
5. If you need to remove multiple special characters, you can nest multiple SUBSTITUTE functions. For example, to remove both "!" and "@", use the formula =SUBSTITUTE(SUBSTITUTE(A1,CHAR(33),""),CHAR(64),"").

Using REGEXREPLACE Function

1. Open your Google Sheet containing the data with special characters.
2. Click on an empty cell where you want to display the cleaned data.
3. Type the following formula: =REGEXREPLACE(A1, "[^a-zA-Z0-9\s]", ""), replacing A1 with the cell that contains the text with special characters.
4. Press Enter to apply the formula.
5. This formula will remove all special characters and keep only letters, numbers, and spaces in the text.

Example

Data With Special Characters

A1: Hello@Wor!ld$
A2: Goo^gle&Shee%ts

Removing Special Characters

1. Click on cell B1.
2. Type the formula: =REGEXREPLACE(A1, "[^a-zA-Z0-9\s]", "")
3. Press Enter to apply the formula.

Result

B1: HelloWorld

4. To apply the formula to more cells, click on cell B1, then move your cursor to the bottom-right corner of the cell until it turns into a crosshair (+). Click and drag the crosshair down to cover the cells you want to clean.

Final Result

B1: HelloWorld
B2: GoogleSheets

Now you have successfully removed special characters from your data in Google Sheets.