Find and replace Google Sheets data and special characters

Standard Google Sheets Find and replace tool

I bet you're familiar with this standard tool available in Google Sheets &mdash Find and replace:

1. You hit Ctrl+H.
2. Enter whatever you need to find.
3. Enter another value you need to see instead.
4. Choose to find and replace in all Google sheets / current sheet / specific range.
5. And press one of the buttons to conduct the search or substitute values one by one or all a time.

This way, you can search for any text or characters, whether they are written in different text cases or take up entire cells. It also lets you do a partial search (using regular expressions) as well as look in formulas and links.

Yes, this set is the minimum required by many of us to find and replace in Google Sheets. But I'd like to mention another tool that is much more powerful and just as easy to use!

Advanced Find and Replace — add-on for Google Sheets

This add-on will take your spreadsheet game to the next level without requiring your effort. It will make even the newbie feel confident in spreadsheets.

The essentials are the same as in the standard tool but with a few cherries on top:

1. You will search & replace 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:
3. You will select any number of spreadsheets to look in.
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:
5. 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:
6. You can even keep the formatting of the changed values!

That's what I call advanced find and repalce in Google Sheets ;) Install the add-on 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 you need to find and replace Google Sheets characters and entering each one of them one by one 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:

1. When there are accented characters (letters with diacritical marks), This tool will turn á to a, é to e, etc.
2. Replacing codes in Google Sheets with symbols and back is extremely useful if you work with HTML texts or simply pull your text from the Web and back:
3. Turn all smart quotes into straight quotes at once without any formulas in extra columns:

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 that you can get for free from the Google Sheets store with more than 40 other time-savers.

Google Sheets SUBSTITUTE function

This first function literally searches for a specific text/character in the desired range and changes it to something different:

• text_to_search is a cell / particular text where you want to make the changes. Required.
• search_for is a text/character you want to take over. Required.
• replace_with is a new text/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 text/character in a cell, you'll be able to pick the one to replace in Google Sheets. Omit the argument — and all instances will be changed.

Now, when you import data from the Web, you may find smart quotes there:

I will use SUBSTITUTE to replace these Google Sheets characters with straight quotes. Since one function finds and changes one character at a time, I'll start with the opening smart quotes:

=SUBSTITUTE(A2,"“","""")

The first formula is looking at A2 searching for opening smart quotes — “ (this must be put in double quotes per the function request) and changes them to straight quotes — "

This looks fine, but I know, it's useless unless there's a way to replace Google Sheets closing smart quotes as well. And there is one :) Just embrace this first formula with another SUBSTITUTE:

=SUBSTITUTE(SUBSTITUTE(A2,"“",""""),"”","""")

The SUBSTITUTE from the inside changes the opening brackets first, and its result becomes the range to work with for the second function instance.

Google Sheets REGEXREPLACE function

REGEXREPLACE is another function at your disposal in Google Sheets to find and replace text or characters.

I will keep my examples with smart quotes to demonstrate how it works.

• 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 in Google Sheets..
• 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,"[“”]","""")

1. The formula looks at A2.
2. Searches for all instances of each character listed between the square brackets: “”

   Note. Don't forget to take the entire regular expression in extra double quotes since it's required by the function.

3. Then it replaces each instance with straight double quotes: """"

   Why are there 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 in Google Sheets by the string from the third argument.

That's why to include that single smart quotation mark in the formula, you must nest 2 Google Sheets 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.