Find and replace special characters in Google Sheets
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.
Find and replace characters using Google Sheets formulas
I'll start with the usual: there are 3 special useful functions that find and replace Google Sheets special characters.
Google Sheets SUBSTITUTE function
This first function literally searches for a specific character in the desired Google Sheets range and replaces it with another specific string:
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
- text_to_search is a cell / particular text where you want to make the changes. Required.
- search_for is a character you want to take over. Required.
- replace_with is a new character you want to get instead of the one from the previous argument. Required.
- occurrence_number is a completely optional argument. If there are several instances of the character, it will let you manage which one to change. Omit the argument — and all instances will be replaced in your Google Sheets.
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,"“",""""),"”",""""),"’","'")
Google Sheets REGEXREPLACE function
REGEXREPLACE is another function I will use to find and replace Google Sheets smart quotes with straight ones.
REGEXREPLACE(text, regular_expression, replacement)
- text is where you want to make the changes
- regular_expression is the combination of symbols (kind of a mask) that will tell what to find and replace.
- replacement is the new text to have instead of the old one.
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,"[“”]","""")
- The formula looks at A2.
- Searches for all instances of each character listed between the square brackets: “”
Note. Don't forget to enfold the entire regular expression with double quotes since it's required by the function.
- And substitutes each instance with straight double quotes: """"
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.
Tools to find and replace Google Sheets characters
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.
Standard Google Sheets Find and replace tool
I bet you're familiar with this standard tool available in Google Sheets:
- You hit Ctrl+H.
- Enter what to find.
- Enter the replacement value.
- Choose between all sheets / current sheet / specific range to process.
- And press Find and Replace or Replace all right away.
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?
Advanced Find and Replace — add-on for Google Sheets
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:
- You will search not only within values and formulas but also notes, hyperlinks, and errors.
- A combination of extra settings (Entire cell + By mask + an asterisk (*)) will let you find all cells that contain only those hyperlinks, notes and errors:
- You can select any number of spreadsheets to look in — each of them can be (de)selected.
- All found records are neatly grouped by sheets in a tree-view letting you replace either all or only the selected records in one go:
- You can even find and replace in Google Sheets by keeping the formatting of the values!
- There are 6 extra ways to deal with the found records: extract all/selected found values; extract entire rows with all/selected found values; delete rows with all/selected found values:
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.
Replace Symbols for Google Sheets — a special add-on from Power Tools
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:
- When you need to replace accented characters in Google Sheets (or, in other words, remove diacritical marks from letters), i.e. turn á to a, é to e, etc.
- Replace codes with symbols and back is extremely useful if you work with HTML texts or simply pulling your text from the Web and back:
- Turn all smart quotes into straight quotes at once:
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.
You may also be interested in
Table of contents
