Find and replace special characters in Google Sheets

Getting tired of all those smart quotes, accented letters, and other unwanted special characters in Google Sheets? We have a few ideas on how to replace them 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 special characters in Google Sheets in one go.

Find and replace characters using Google Sheets formulas

I'll start with the usual: there are 3 functions that I find especially helpful when it comes to finding and replacing special characters in Google Sheets cells.

SUBSTITUTE

The first is SUBSTITUTE. It literally searches for a specific character in the desired 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 replace. 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 replace. Omit the argument — and all instances will be replaced.

Now, when you import data from the Web, you may find smart quotes there:
Import data with smart quotes.
Let's use SUBSTITUTE to replace them with straight quotes. Since one function looks for and replaces one character at a time, I'll start with the opening smart quotes:

=SUBSTITUTE(A2,"“","""")
Find opening smart quotes and replace them with straight quotes in Google Sheets using the SUBSTITUTE function.
See? I'm looking at A2, search for opening smart quotes — “ (that must be put in double quotes per the function request), 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,"“",""""),"”","""")
Enfold one SUBSTITUTE into another to replace more characters at a time.
The SUBSTITUTE inside replaces the opening brackets first, and its result becomes the range to work with for the second SUBSTITUTE.

Tip. The more characters you want to find and replace, the more SUBSTITUTE functions you'll need to thread. Here's an example with an extra single smart quote:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"“",""""),"”",""""),"’","'")

REGEXREPLACE

REGEXREPLACE is another function I will use to find and replace 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 opening and closing smart quotes:

=REGEXREPLACE(A2,"[“”]","""")
Find and replace smart quotes using Google Sheets REGEXREPLACE.

  1. The formula looks at A2.
  2. 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.
  3. And replaces 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 replaced by 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,"[“”]",""""),"’","'")
Nest multiple REGEXREPLACE functions to find various characters and replace with different equivalents.
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 for Google Sheets to find and replace 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 Find and replace tool from Google Sheets

I bet you're familiar with the standard search and replacement tool for Google Sheets:

  1. You hit Ctrl+H.
  2. Enter what to find.
  3. Enter the replacement value.
  4. Choose between all sheets / current sheet / specific range to process.
  5. And press Find and Replace or Replace all right away.

Find and replace tool from Google Sheets.
Nothing special here — this is the minimum required by many of us to find and replace data in spreadsheets successfully. But what if I told you that this minimum could be extended without posing even the slightest difficulty in use?

Advanced Find & Replace — add-on for Google Sheets

Imagine the tool more powerful than the standard Find and replace. Would you like to try it? I'm talking about our Advanced Find & 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:

  1. You will search not only within values and formulas but also notes, hyperlinks, and errors.
  2. 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:
    Find all cells with notes, hyperlinks, errors.
  3. You can select any number of spreadsheets to look in — each of them can be (de)selected.
  4. 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:
    Find and replace all errors, links, and notes.
  5. You can even search and replace by keeping the formatting of the values!
  6. 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:
    Export and delete 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 & Replace from the spreadsheets store (or find it in 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:
Replace accented characters, codes, symbols, smart quotes.

  1. When you need to replace accented characters (or, in other words, remove diacritical marks from letters), i.e. turn á to a, é to e, etc.
  2. 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:
    Replace codes with their symbols and back.
  3. Turn all smart quotes into straight quotes at once:
    Find and replace all smart quotes in Google Sheets with straight quotes.

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