How to Find and Replace in Google Sheets

puneet-gogia-excel-champs

- Written by Puneet

In Google Sheets, Find and Replace is a powerful feature that allows users to search for specific content within their spreadsheet and replace it with new content. This tool is handy for editing large datasets, correcting errors, or making bulk updates to a set of data.

In this tutorial, we will learn to use Find and Replace option in Google Sheets in detail.

Open Find and Replace in Google Sheets

There are two ways to open the Find and Replace option in Google Sheets. The first one is to open the “Find and Replace” option by going to the Edit menu in the top menu bar and selecting Find and replace.

Apart from this, if you are on a PC, you can easily open the Find and Replace using a shortcut on your keyboard. Press the “Ctrl” key and the “H” key simultaneously (Ctrl + H).

On the other hand, if you use a Mac, the process is slightly different. Instead of using the “Ctrl” key, you will use the “Command” key. Press the ‘Command’ key, the “Shift” key, and the “H” key simultaneously (Command + Shift + H).

Only Open Find Option

You can open the “Find” option by using the keyboard shortcut, Ctrl + F (In Windows) and Cmd + F (In Mac).

The “Find” options is primarily used to search for a specific value within the sheet. And the “Find and Replace” is a more advanced option that locates the data you’re searching for and allows you to replace it with something else.

Using Find and Replace in Google Sheets

Once you open the find and replace option, you can use the below steps to use it and replace the values want to replace from the data.

  1. Type What to Find: In the Find box, type the exact word or number you want to change. For example, if you’re correcting the misspelled word “helo” to “hello” type “helo” here.
  2. Enter Replacement Text: In the Replace with box, type what should appear instead of the text you find. Following our example, you would type “Hello”.
  3. Set Your Preferences – You can select whether to search in the current sheet or all sheets in the workbook. You can the setting from the drop-down if you want.
  4. Find or Replace – There are two options to use here. One is to use the “Find” which will only find the values. And second is “Replace” and “Replace All” which will replace the values with the new value.

Match Case

The “Match case” option in the Find and Replace tool helps you search for text with exact capitalization, differentiating between uppercase and lowercase letters.

In the Find box, enter the word you’re looking for. Make sure you use the exact word with uppercase and lowercase letters you want to find.

Check the box next to “Match case” to make sure your search only finds text that exactly matches the capitalization you entered.

In our example, we have the word “HELO” to find, and we need to replace it with the “Hello”. But in the data we have three words, but only one is exact match of the “HELO”.

Match Entire cell content

When you turn on this option, the Find and Replace function will only consider cells where the text exactly matches what you typed in the “Find” field.

It won’t replace text in a cell if the text you’re searching for is just part of the cell value.

In the above example, you can see when you try to replace the work “Yes” and then “Match entire cell contents” is check marked. Now it only, replace the value from the cell A4, not from the cell A2.

All Sheets Vs. This Sheet Vs. Select Range

In Google Sheets, the Find and Replace option allows flexibility for where to perform your searches and replacements. You can choose to search in “All sheets”, “This sheet”, or within a “Specific range”.

  • All Sheets – The “All sheets” option allows you to search and replace text across all the sheets within your Google Sheets. This is useful when updating or correcting information that may be spread across multiple sheets in your workbook.
  • This Sheet – The “This sheet” option restricts the search to the currently active sheet. This default setting is helpful for when you want to find and replace within a single worksheet.
  • Specific Range – The “Select range” option allows you to define a specific range of the sheet to find and replace. By selecting this option, you can specify a range, a row, a column.

Search using regular expressions.

Regular expressions are a sequence of characters that define a search pattern, primarily used for string matching.

This provides a way to search for complex patterns in text. Let’s say you want to replace all the words from a sheet which start with the letter “H” with the work “Hey”.

Now for this you can use the regular expressions ^H[a-zA-Z ]+$ in the “Find” input bar. And in the Replace with enter the word “Hey”.

And now when you hit enter, it replaces all the words in the sheet which start with the letter “H” with “Hey”.

Note – Regex can be complex and easy to misread. First, test your patterns in a small, controlled range of a sheet.

Here’s a table summarizing the metacharacters, their meanings, and examples of how they can be used in Find and replace.

Metacharacter
Description
Example Pattern
Example
.
Matches any single character except newline
a.c
Matches “abc”, “arc”, “a&c”, etc.
^
Matches the start of a line
^Hello
Matches “Hello” if at the start of a line
$
Matches the end of a line
world$
Matches “world” if at the end of a line
*
Matches 0 or more of the preceding element
a*
Matches “a”, “aa”, “aaa”, etc., and the beginning of “alone”
+
Matches 1 or more of the preceding element
a+
Matches “a”, “aa”, “aaa”, etc., but not an empty string
?
Makes the preceding element optional (0 or 1)
colou?r
Matches “color” and “colour”
{n}
Matches exactly n occurrences of the preceding element
a{3}
Matches “aaa”
{n,}
Matches n or more occurrences of the preceding element
a{2,}
Matches “aa”, “aaa”, “aaaa”, etc.
{n,m}
Matches between n and m occurrences of the preceding element
a{2,3}
Matches “aa”, “aaa”
[]
Matches any single character included between the brackets
[a-zA-Z]
Matches any uppercase or lowercase letter
[^ ]
Matches any single character not included between the brackets
[^a-zA-Z]
Matches any character that is not an uppercase or lowercase letter
\
Escapes a metacharacter (makes it a literal)
\.
Matches a period “.”
`
`
Alternation (or operator)
`cat
()
Groups patterns together and captures the text matched
(abc)+
Matches “abc”, “abcabc”, “abcabcabc”, etc.

Special Character Combination

These are some quick character combinations for regex that match specific types of characters:

Character
Meaning
\d
Matches any digit (equivalent to [0-9])
\D
Matches any non-digit (equivalent to [^0-9])
\w
Matches any word character (letters, digits, and underscores)
\W
Matches any non-word character (opposite of \w)
\s
Matches any whitespace character (spaces, tabs, etc.)
\S
Matches any non-whitespace character

Searching with the formulas

When you tick mark this option, it searches for and replaces text within cell values and inside any formulas you might have in your sheet. It is particularly useful for those who work extensively with formulas.

In the below example, we have a formula in the cell C1 and now from the formula we need to replace the reference of the cell B1 with A2. Now the moment you checkmark the options, show the formula from the cell instead of the result.

And now when you click “Replace” it changes the cell reference from formula from B1 to A2.

But while this option you need to take case as an incorrect replacement could break your sheet’s functionality. And after making replacements, carefully test your sheet’s formulas to ensure everything is working as expected.

When you tick-mark this option, you can search within hyperlinks, not just text and numbers. This is very useful when you want to update multiple links in your workbook simultaneously.

Let’s say you have hyperlinks for the website, https://abc.com with a new website link (https://xyz.com).

All you need to do is to open the find and replace (Ctrl + H) and then:

  • Find: https://abc.com
  • Replace with: https://xyz.com

And make sure to tick-mark the “Also search within links” and then click “Done” to replace.

Important Points

Here are some important points to keep in mind:

  • Make sure to select scope in which you want to replace the values, in the current sheet, or the entire workbook, or in specific range.
  • Before you begin using Find and Replace, especially if you are making bulk changes, it’s crucial to backup your spreadsheet.
  • Before clicking on “Replace all,” use “Find” and “Find next” to review each value manually.
  • When changing cell references within formulas, understand that even small changes can break your formulas. Double-check formula logic after replacements.
Last Updated: April 28, 2024