Mehrab Imtiaz
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:
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.
Our formula
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:
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.
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.
Read More: How to Remove Comma in Google Sheets (3 Easy Ways)
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:
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.
Using REGEXREPLACE Function
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:
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.
Read More: Use REGEXREPLACE to Replace Multiple Values in Google Sheets (An Easy Guide)
Similar Readings
- How to Find Median in Google Sheets (2 Easy Ways)
- Find Frequency in Google Sheets (2 Easy Methods)
- How to Find Edit History in Google Sheets (4 Simple Ways)
- Find Largest Value in Column in Google Sheets (7 Ways)
- How to Find Trash in Google Sheets (with Quick Steps)
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.
Clicking on Replace all will remove all special characters from the range in Google Sheets.
Read More: How to Use Find and Replace in Column 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.
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:
=REGEXREPLACE(B2,".*,.*(\+.*)","$1")
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.
=REGEXREPLACE(B2,"\,.*","")
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.
Read More: How to Remove Numbers from a String in Google Sheets
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
- How to Search in Google Spreadsheet (5 Easy Ways)
- Find Uncertainty of Slope in Google Sheets (3 Quick Steps)
- How to Find Correlation Coefficient in Google Sheets
- Replace Space with Dash in Google Sheets (2 Ways)
- How to Find Linear Regression in Google Sheets (3 Methods)
- Find All Cells With Value in Google Sheets (An Easy Guide)
- How to Find Hidden Rows in Google Sheets (2 Simple Ways)
- Find and Replace with Wildcard in Google Sheets
