by Natalia Sharashova, updated on
Getting tired of all those smart quotes, accented letters, and other unwanted special characters? We have a few ideas on how to find and replace them in Google Sheets effortlessly.
We split cells with text in spreadsheets, removed and added various characters, changed the text case. Now it's high time to learn how to find and replace Google Sheets special characters in one go.
I'll start with the usual: there are 3 special useful functions that find and replace Google Sheets special characters.
This first function literally searches for a specific character in the desired Google Sheets range and replaces it with another specific string:
Now, when you import data from the Web, you may find smart quotes there:
Let's use Google Sheets SUBSTITUTE to find and replace them with straight quotes. Since one function looks for and substitutes one character at a time, I'll start with the opening smart quotes:
=SUBSTITUTE(A2,"“","""")
See? I'm looking at A2, search for opening smart quotes — “ (that must be put in double quotes per the function request in Google Sheets), and replace it with straight quotes — "
Note. Straight quotes are not only wrapped in double quotes but there's also another " appended so there are 4 double quotes in total.
How do you add closing smart quotes to this formula? Easy :) Just embrace this first formula with another SUBSTITUTE:
=SUBSTITUTE(SUBSTITUTE(A2,"“",""""),"”","""")
The SUBSTITUTE inside changes the opening brackets first, and its result becomes the range to work with for the second function instance.
Tip. The more characters you want to find and replace in Google Sheets, the more SUBSTITUTE functions you'll need to thread. Here's an example with an extra single smart quote:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"“",""""),"”",""""),"’","'")
REGEXREPLACE is another function I will use to find and replace Google Sheets smart quotes with straight ones.
Basically, the drill here is the same as with SUBSTITUTE. The only nuance is to build the regular_expression correctly.
First, let's find and replace all Google Sheets opening and closing smart quotes:
=REGEXREPLACE(A2,"[“”]","""")
Note. Don't forget to enfold the entire regular expression with double quotes since it's required by the function.
Why there are 2 pairs of double quotes? Well, the first and the last ones are required by the function just like in the previous argument — you simply enter everything between them.
A pair inside is one double quote duplicated for the sake of being recognized as a symbol to return rather than the mark required by the function.
You may wonder: why can't I add a single smart quote here as well?
Well, because while you can list all characters to look for in the second argument, you can't list different equivalents to return in the third argument. Everything that is found (from the second argument) will be change to the string from the third argument.
That's why to include that single smart quotation mark in the formula, you must thread 2 REGEXREPLACE functions:
=REGEXREPLACE(REGEXREPLACE(A2,"[“”]",""""),"’","'")
As you can see, the formula I used earlier (here it's in the middle) becomes the range to process for another REGEXREPLACE. That's how this function finds and replaces characters in Google Sheets step by step.
When it comes to finding and replacing data in Google Sheets, formulas are not the only option. There 3 special tools that do the job. Unlike formulas, they don't require any additional columns to return the results.
I bet you're familiar with this standard tool available in Google Sheets:
Nothing special here — this is the minimum required by many of us to find and replace in Google Sheets successfully. But what if I told you that this minimum could be extended without posing even the slightest difficulty in use?
Imagine the tool more powerful than Google Sheets standard Find and replace. Would you like to try it? I'm talking about our Advanced Find and Replace add-on for Google Sheets. It will make even the newbie feel confident in spreadsheets.
The basics are the same but with a few cherries on top:
That's what I call advanced search and replacement in Google Sheets ;) Don't take my word for it — install Advanced Find and Replace from the spreadsheets store (or have it as part of Power Tools along with the Replace Symbols tool described below). This help page will guide you all the way.
If entering each symbol you want to find and replace in Google Sheets is not an option, Replace Symbols from Power Tools may help you out a bit. Just don't judge it by its size — it's powerful enough for certain cases:
In all three cases, you just need to select the range, pick the required radio button and hit Run. Here's a demo video to back up my words ;)
The add-on is part of Power Tools which can be installed to your spreadsheet from the Google Sheets store with more than 30 other time-savers.
Table of contents