Skip to Content

Ways to Use Google Sheets Find and Replace Tool (with Examples)

Google Sheets Find and Replace feature lets you deal with large volumes of data, which constantly changes with time. You need to go to the “Edit” tab of the main and select the last option named “Find and replace” from the popup to access this feature.

Manually searching a text string in a spreadsheet that contains thousands of rows is time-consuming.

A Find and Replace tool can automate this process for you.

Few of you have used the find and replace tool quite often to update old values with new ones in programs like Google Docs, Microsoft Word, etc.

But what if I will tell you that it was less than half of what the same feature can do in Google Sheets?

In this article, let us explore different ways to use the Google Sheets Find and Replace feature. We will dive deep into the tool and learn to extend its capabilities with regular expressions, so make sure to read the article till the end.

Download The Example Google Sheet

You can download the example Google Sheet, which contains the dataset we used to demonstrate the different ways to use a Find and Replace tool. By practicing the examples discussed in the following sections, you will strengthen your knowledge of the tool.

Click Here to Copy Example Sheet!

If you have your own Google Sheet ready with the required dataset, then skip downloading the above file and follow me along.

Explained: Find and Replace tool in Google Sheets

Google Sheets Find and Replace tool allows you to make multiple edits quickly and effectively.

In contrast to other spreadsheet programs, the Google Sheets Find and Replace tool boasts additional options to take the search to the next level.

Here’s the screenshot of the Find and Replace tool in Google Sheets,

It allows you to strengthen your search for the given text string with the following options:

  • Match case – You can tick the box before this option to make your search case-sensitive.
  • Match entire cell contents – A cell may contain a large amount of text. With this option, you will search for those cells whose content length is similar to the text string defined in the “Find” box.
  • Search using regular expression – This option lets you leverage regular expressions to find the cells that follow a certain pattern. You can define the text string using the regular expression in the “Find” box and tick the box before this option.
  • Also search within formulas – It is a really great option that is useful in cases where the cell contains a formula.
  • Also search within links – This is a new option that lets users find a text string within links. Suppose you have a cell containing a text string which is linked to another spreadsheet or a third-party website. This option compares the text string and link as well to return the search results.

We will learn to use each of these options using examples in the following sections of this article.

Users can choose to replace all the instances of the search one by one or at once.

Replacing all the instances at once will hardly take a minute. But make sure to verify the search is configured properly using the options discussed above.

How to use Google Sheets Find and Replace tool

Now, let us discuss the steps to use the Find and Replace tool in Google Sheets.

We will consider different scenarios as below.

1. Using the Find and Replace tool to update a text string with an exact match

Here’s the example data where we have employee names in the first column and their department in the second column.

Our task is to replace the keyword “IT” in the entire spreadsheet with “Information Technology”.

Let’s begin,

  • Select any cell from the spreadsheet
  • Hover to the main menu and click on “Edit” tab
  • Choose the “Find and replace” option from the popup
  • A new dialog box will be displayed on the screen, as shown below
  • Click on the empty box beside the “Find” option
  • Type “IT
  • Next, click on the empty box beside the “Replace” option
  • Type “Information Technology
  • To find and replace the “IT” keyword within the current spreadsheet, you need to click on the dropdown beside the “Search” option
  • Select “This sheet” from the popup
  • Tick the box before “Match case
  • Now, click on the “Replace all” button as shown in the following image
  • A message will be displayed as “Replaced 20 instances of IT with Information Technology”. Refer to the following image
  • Press the “Done” button in the green to close the dialog box

As per the message displayed after clicking the “Replace all” button, there were 20 employees (or cells) who were working in the IT department.

It was a simple scenario where we did a case-sensitive search using the “Match case” option of the Find and Replace tool.

What is another way to access the Find and Replace tool in Google Sheets?

You can access the Find and Replace tool either from the “Edit” tab of the main menu or using the keyboard shortcut “CTRL + H” on Windows and “CMD + H” on Mac.

Both are quickest, so you can choose as per your preference.

Note that in contrast to Microsoft Excel, where we use “CTRL + F” to access the Find and Replace tool, Google Sheets uses “CTRL + H“.

If you press “CTRL + F” while using Google Sheets, it will open the search tool. Refer to the following image. The search tool appears at the upper-rightmost corner of the screen.

By clicking on the three dots that appear at the extreme right side of the search tool, you can open the Find and Replace dialog box as well.

2. Using the Find and Replace tool to update cells whose length is equal to the searched text string

Consider the following dataset where we have employee names in the first column, their job titles in the second column, and departments in the third column.

Now, for a few employees working in the IT department, the designation is written as “IT Systems Architect”.

Suppose we use the previous steps to replace the keyword “IT” using the Match case option of the Find and Replace tool. In that case, it will update “IT Systems Architect” to “Information Technology Systems Architect”.

But, we don’t want it that way.

Our job is to convert the “IT” keyword from the Department column to “Information Technology”.

Here, we need to use the second option named “Match entire cells content” of the Find and Replace tool.

Let’s begin,

  • Select any cell
  • Hover to the main menu and click on “Edit” tab
  • Choose the “Find and replace” option from the popup
  • A new dialog box will open, as shown in the following image
  • Type “IT” in the empty box beside the Find option
  • Type “Information Technology” in the empty box beside the Replace option
  • Click on the dropdown beside the Search option and select “This sheet” from the list, as shown below
  • Tick the box before “Match entire cells contents
  • Now, click on the “Replace all” button at the bottom of the dialog box
  • A message will be displayed as “Replaced 20 instances of IT with Information Technology“. Refer to the following image

    (This message means that there are a total of 20 employees working in the IT department. After using the Find and Replace tool, the department of 20 employees has been changed from “IT” to “Information Technology”)
  • Press the “Done” button to close the dialog box

Now, after looking closely, you will notice that the cells containing “IT Systems Architect” haven’t been updated as we had configured the Find and Replace tool to search for the cells that include the “IT” keyword and have the same length as our search query.

Simply put, you have just replaced the cells, including the text string, as “IT” only.

3. Using the Find and Replace tool to update the cells that follow a certain pattern

In this method, we will use the power of Regular expressions.

For those who don’t know,

Regular Expression is also known as REGEX. It helps users find the cells that follow a certain pattern.

For example, when your spreadsheet contains multiple products that start with the letter “A”, you can use regular expressions to identify all those products.

It uses special symbols called Metacharacters and Anchors to locate patterns. Here are a few of them for your reference.

Character TypeDescription
^AnchorHelps you match the search with the beginning of the text string
$AnchorHelps you match the search with the end of the text string
.MetacharacterFinds all the cells holding single characters defined by the users
|MetacharacterThe “OR” operator to find cells that contain one of the multiple text strings defined by the users

There are 20+ symbols that are used to find the cells that follow certain criteria. We won’t discuss them all because it is a different topic.

For the sake of this article, we consider a simple example as shown below.

Our task is to find all the employee names that start with “R” in each department. We have a record of 100 employees. Let us replace all those employee names with “classified”.

Here are the steps,

  • Select any cell of the spreadsheet
  • Hover to the main menu and click on “Edit” tab
  • Click on the “Find and replace” option of the popup
  • A new dialog will appear, as shown below
  • Type “^R[a-zA-Z ]+$” in the empty box beside the Find option

    (Note that the caret [“^”] symbol is a part of the regular expression, which will identify all the cells whose text string starts with the letter R. Next part [“[a-zA-Z ]+$]” tells the tool to consider the entire string instead of the letter R only while replacing the cell content with other keyword defined by the user) 
  • Type “classified” in the empty box beside the Replace option
  • Now, the names are in the first column titled Full Name. We will make sure that the tool will replace the cells that start with the letter R in the first column only
  • Click on the dropdown beside the Search option and select “Specific range
  • A new box will appear beside the dropdown, as shown below
  • Click on the Table icon of the box
  • A new popup will be displayed
  • Select the entire first row as shown below
  • Press the “OK” button, and you will be taken back to the Find and Replace tool
  • Next, tick the box before the “Search using regular expressions” option
  • You will notice that the box before the option “Match case” is automatically ticked. Keep it as is
  • Click on the “Replace all” button
  • You will notice a new message above the buttons, as shown in the following image. It says, “Replaced 5 instances of ^R[a-zA-Z ]+$ with classified

    (In other words, there are five employees whose name starts with the letter “R”)
  • Finally, press the “Done” button to close the Find and Replace tool dialog box

All the names that start with the letter “R” are instantly replaced with “classified”. Refer to the following image for results.

Note that regular expressions are hard to learn, but once you know how to use them, it will surely take your Google Sheets skills to the next level.

4. Using the Find and Replace tool to search a text string within the formula

Generally, the Find and Replace tool searches for the text string within the cell content or results generated by a formula within the cell.

But now, let us learn to search a text string within the formula.

We have employee data with their salaries and holiday bonuses. Those who have received the bonus are marked with “Yes” in the fifth column named “Bonus Received”.

It uses a formula to identify the employees who have received the bonus.

Our task is to replace the keyword “No” with “Better Luck Next Time“.

Here are the steps,

  • Select any cell of the spreadsheet
  • Hover to the main menu and click on “Edit” tab
  • Choose “Find and replace” from the popup
  • Type “No” in the empty box beside the Find option
  • Type “Better Luck Next Time” in the empty box beside the Replace option
  • To make the changes within the fifth column of the current spreadsheet, click on the dropdown beside the “Search” option and select “Specific range” from the list
  • Now, you will notice a new box beside the dropdown, as shown in the following image
  • Click on the Table icon
  • A new popup will appear
  • Select the entire fifth row named “Bonus Received
  • Click the “OK” button
  • You will be taken back to the Find and Replace tool dialog box
  • Tick the box before “Also search within formulas
  • Click on the “Replace all” button
  • You will notice a message as “Replaced 100 instances of No with Better Luck Next Time
  • Press the “Done” button to close the Find and Replace tool dialog box

Now, if you click on any formula from the column Bonus Received, it will be replaced as shown below,

5. Using the Find and Replace tool to search a text string within hyperlinks

Consider the following dataset, where we have a list of websites. A few of the hyperlinks in the table contain links to the blog page of the respective website.

Our task is to delete that part of the hyperlink and make sure that when we click on those links, we will be taken to the homepage of the website.

Let’s begin,

  • Select any cell from the spreadsheet
  • Hover to the main menu and click on the “Edit” tab
  • Choose “Find and replace” from the popup
  • A new dialog will open, as shown below
  • Type “blog” in the empty box beside the Find option
  • We won’t type anything in the empty box besides the Replace option
  • Now, click on the dropdown beside the Search option and select “This sheet” from the list
  • Tick the box before “Also search within links
  • Next, click the “Replace all” button
  • You will notice a message “Replaced 4 instances of blog with  “
  • Press the “Done” button

Now, you will see that all the hyperlinks have been updated and will redirect the users to the homepage of the website.

Conclusion

That’s all about using the Find and Replace tool in Google Sheets.

It is a really powerful tool that allows users to replace any number of instances at once or even one by one.

In the case of complex datasets, you can combine various options such as “Match entire cell contents” and “Search using regular expressions” of the Find and Replace tool.

Practice is the key here. You will master this tool with practice. So make sure to play around with the example Google Sheet you downloaded at the beginning of this article.

Feel free to comment below if you are stuck somewhere or having any particular issue while using the Find and Replace tool in Google Sheets.

Click here to explore our blog for more tips and tricks of using Google Sheets like a pro.