How to Remove Special Characters in Google Sheets (3 Easy Ways)

When we think about deleting anything from a spreadsheet, we think about deleting the contents of an entire cell. But sometimes we might need to remove partial values from a cell, either out of need or for security purposes. Today, we will look at how we can remove special characters in a Google Sheets cell.

How to Remove Special Characters in Google Sheets

We can consider special characters to be anything that is not numbers or text. It can be punctuations or any symbols that you may find when you look down at your keyboard.

This means that Google Sheets does not recognize them as a part of a group and they have to be handled individually. And thus, we start with some common punctuations.

1. Remove Common Punctuations from a Cell in Google Sheets

Let’s consider the following string in a cell:

string to remove special characters from in google sheets

We have in it some common punctuations like a period (.) and comma (,), as well as other symbols like the colon (:) and hyphen (-).

To remove the commas, we will use the SUBSTITUTE function.

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

substitute function syntax

Our formula


using substitute function to remove punctuation in google sheets

Formula Breakdown:

  • B3 is the cell that contains our string.
  • The SUBSTITUTE function looks for the character “,” in the string. This field is used to find any special characters available in Google Sheets.
  • The function replaces it with a blank space (“”). This typically acts as the removal of the special character.

The function itself cannot take more than one value to substitute. So, if we want to remove more than one special character of punctuation, we have to get creative.

Our new formula to remove both commas and the period:


removing multiple special characters in google sheets using the substitute function

As you can see, we have nested one SUBSTITUTE function in another to take advantage of it being a text function and cover multiple conditions.

You can do this as many times as you want to remove more special characters in Google Sheets.

using nested substitute functions to remove multiple special characters

Removing comma, period, and hyphen with SUBSTITUTE function

Using Unicode to Remove Special Characters

If you know the character codes for the symbols you are trying to remove, the SUBSTITUTE function can also be used with them.

These codes can be found in any latest Unicode table on the internet. It should be the decimal value.

To apply these codes you have to use the CHAR function. The code for comma is 44.


using substitute function with character code

2. Remove Non-Numeric and Non Text Characters in Google Sheets

The primary difficulty faced by users when removing special characters is the fact that these characters are often coupled with others that they don’t want to remove.

We can see an example of this in the following image:

text with special characters

To remove these special characters from the text we must take the help of regular expressions. Regular expressions are a special sequence of characters that help Google Sheets fine-tune searches.

Since we have multiple symbols to remove, we will utilize the set feature of regular expressions. This is denoted by square braces [].

The idea is to put all the special characters or symbols within the [] to form a set. For example, if we want to find “*”, “@”, and “#”, we simply write [*@#].

Now let’s look at the two methods where we can use this regular expression to remove these special characters.


As the name suggests, the REGEXREPLACE function can use a regular expression to find and replace data.

Applying what we have just learned into the function, we get:


using regexreplace function to remove multiple special characters in google sheets at once

We have successfully removed the three special characters (“*”, “@”, and “#”) from the text. To remove all the special characters, we have only to add them to the set.

Note: If you want to remove the hyphen/dash (-) it is a good idea to add the symbol at the end of the set. Otherwise, we will act as a range and not an individual symbol.


removing all special characters from the cell using regexreplace

Using Find and Replace Feature

Alternatively, we can use the Find and Replace feature of Google Sheets to remove special characters using regular expressions.

You can access the Find and Replace window by using the keyboard shortcut CTRL+H or find the option under the Edit tab.

Apply the following conditions:

  • Find [*@#!,-]. This is our regular expression set that will help us find all the special characters in the cells.
  • Leave the Replace with field blank. This will replace the found special characters with a blank.
  • We are searching over a Specific range to better control which cells we are working with.
  • Check the Search with regular expression option. Otherwise, Find and Replace will not recognize our set.

conditions for find and replace for regular expressions

Clicking on Replace all will remove all special characters from the range in Google Sheets.

using find and replace to remove special characters in google sheets

3. Remove Everything Before or After a Character in Google Sheets

Special characters in Google Sheets can also act as breakpoints when removing characters from a cell. Of course, we once again take advantage of regular expressions and the REGEXREPLACE function.

For example, we have the following dataset consisting of Name and Phone number information.

information dataset

First, we want to remove everything before a certain character. Meaning that we want to only extract the phone number. To do that we have to delete the text and characters before the +. Thus, our formula will be:


removing all characters before a special character

Formula Breakdown:

  • The first part of our regular expression is “.*,.*”. This basically helps split the string in two at the comma. So, we set a condition to remove everything before and after the comma.
  • The second part of our regular expression is “(\+.*)”. This means that we will take anything after the plus (+) sign.
  • The $1 as our replacement condition makes the function return the only group from the second section of the argument.

We can do something similar if we want to remove everything after a certain character. For example, if we want only the names from the cell, we must remove all characters starting from the comma.


remove everything after a certain character

Since this time, we are only taking a few characters from the beginning of the string, we do not need a complicated regular expression.

Our regular expression “\,.*” extracts all characters after the comma. We replace these characters with a blank, completely removing them from our cell.

Final Words

That concludes all the ways we can use to remove special characters in Google Sheets. While the SUBSTITUTE function works best with one or a couple of special characters, you can take advantage of regular expressions to remove a set of special characters at once.

Please feel free to leave any queries or advice you might have in the comments section below. Or have a look at some of our other articles where we take advantage of the search functionalities of Google Sheets in different scenarios.

Related Articles for Reading

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply