Find and Replace in Google Sheets

What Is Find and Replace in Google Sheets?

Find and Replace in Google Sheets helps you search through workbooks or worksheets for specific values and replace them with the values you supplied. Using this feature, you can replace all instances of your search string at once or replace them one by one. For instance, in the sheet below, we want to replace 16 with the word “sixteen.” To do so, go to Edit – Find and Replace. You get the dialog box shown below.

Find-and-Replace-in-Google-Sheets-Definition

Enter the required values in the Find and Replace with boxes, respectively. Press Replace All. You will find that all instances of 16 are replaced with “sixteen,’ as shown below.

Find-and-Replace-in-Google-Sheets-Definition-1
Key Takeaways
  1. Find and Replace in Google Sheets helps us to substitute a specific value with another value. This feature lets us replace every occurrence of a particular search value all at once or one at a time.
  2. Find and Replace can be implemented in 3 different ways.
    • By going to Edit – Find and Replace
    • Using the keyboard shortcut CTRL+H
    • Using the keyboard shortcut CTRL+F
  3. There are several options to choose from when you Find and Replace values like match case, search within formulas, search using regular expressions, and so on.

How to Access the Find and Replace in Google Sheets?

Accessing the Find and Replace in Google sheets is very simple. There are three ways to find the supplied value and replace it with the given value.

  1. Going to Edit – Find and Replace
  2. Using the keyboard shortcut CTRL+H (Windows)
  3. Using the keyboard shortcut CTRL+F (Windows)

Let us look at an example. Below, we have the details of a list of employees at an organization, along with their designations. 

How-to-Access-the-Find-and-Replace-in-Google-Sheets

Step 1: Let us replace the designation of the employees in their abbreviated form. The following values are the replacements we will make.

  1. Software Engineers – SWE
  2. Researcher – RE
  3. Consultant – CT
  4. Manager – MG

Step 2: Let us follow the steps as shown below. Go to the menu and Choose Edit.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-2

Step 3: In the drop-down menu, choose the “Find and Replace” option.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-3

Step 4: You get a Find and Replace dialog box. Here, let us first enter the value “Software Engineer” in the Find box. We must replace this with the SWE value. So, type SWE in the Replace with box.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-4

Step 5: There are two options. You can either replace all the values at one go or replace them one by one using the Find button. Let us first click on the Find button.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-5

Step 6: The first instance of occurrence of the word “Software Engineer” is highlighted.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-6

Step 7: You can choose from any of the options shown below. Here, we click on Replace.

How to Access the Find and Replace in Google Sheets-Step-7

Step 8: You can see how the first value has been replaced. The second occurrence of Software Engineer is now highlighted.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-8

Step 9: Continue clicking on Replace till all the values are replaced. Once it is done, you get this message.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-9

Step 10: Thus, as observed, it is a simple and easy method to replace any value you want. Let us use Control + H and see what happens.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-10

Step 11: The “Find and Replace” dialog box pops up again. Let us now press Ctrl + F. A small “Find” box pops up. Press on the three dots to its right, as shown below.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-11

Step 12: You get the Find and Replace dialog box again. Now, let us try to use the “Replace All’ option for the term Manager.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-12

Step 13: Click on the button “Replace All.” It will ask for confirmation to replace the value across all sheets. Click OK to continue.

How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-13
How-to-Access-the-Find-and-Replace-in-Google-Sheets-Step-13-1

You notice all instances of Manager have been replaced with MG. Thus, the Find and Replace in Google Sheets option is easy to understand and use.

Examples

If you think this is all to Find and Replace, think again! Here, we bring you different examples of how to find and replace in Google sheets in different ways.

Example #1 – Find a Partial Match in a Worksheet

Let us consider the previous worksheet. Here, we have the details of the employees of an organization and their designations.

Find-and-Replace-in-Google-Sheets-Example-1

Step 1: Let us find a partial match for the term “Cons” in the worksheet. Go to Edit and click on the Find and Replace option. You will get the “Find and Replace” dialog box. Here, type “Con” in the Find box and choose the option “This Sheet” under “Search.”

Example-1-Step-1

Step 2: Press Find. You will find the first occurrence of the word containing “Con.” You will find the first occurrence of the word containing “Con.” It will find all the words that contain “Con,” thus completing a partial match in the worksheet.

Example-1-Step-2

Example #2 – Find a Partial Match in a Workbook

Let us add another sheet to the previous workbook with some details, as shown below.

Find-and-Replace-in-Google-Sheets-Example-2

Let us take the earlier example and find a partial match. Go to the original sheet, which is Sheet2. To find a partial match in a workbook, choose the option “All Sheets” under “Search.”

Find-and-Replace-in-Google-Sheets-Example-2-1

Step 1: Now, press Find. It will find all the instances of words containing “Con” in Sheet2. Once done, it will go to Sheet3 and find the word containing “Con.” Thus, it will search all the worksheets in this workbook for a partial match with “Con.”

Example-2-Step-1

Example #3 – Find an Exact Match

Now, we want to find only the cells containing the exact match and not partial searches.

Step 1: Open the dialog box by pressing Ctrl +H. In the dialog box that appears, type the word you want to find, “Researcher” in this case. Since we are looking at the entire workbook, we choose “All Sheets” under “Search.” Finally, check the box that says, “Match Entire Cell Contents.”

Example-3-Step-1

Step 2: Let us search for the term “Researcher”. Press Find. It finds all instances of “Researcher” in the sheets.

Example-3-Step-2

You will notice that it does not highlight the Asst. Researcher in Sheet3, thereby performing a exact match across sheets.

Example-3-Step-2-1

Example #4 – Replace the Range Reference of a Formula

Let us take an example where we have the term $D$2 in a formula. This formula is used in several cells across the worksheet.

Find-and-Replace-in-Google-Sheets-Example-4

Step 1: As observed, $D$2 is used for calculation across Column C. Now, we must move the value in D2 to E2. Hence, we must replace the $D$2. Open the Find and Replace dialog box.

Example-4-Step-1

Step 2: As seen in the image above, enter the details in the respective Find and Replace boxes and check the box for “Also search within formulas.” All the formulas in the sheet (Column C) will immediately be displayed, as shown below.

Example-4-Step-2

Step 3: Press “Replace All.” Here, we can choose either “This Sheet’ or “All Sheets” based on preferences. You will find $D$2 replaced by $E$@ across all cells containing formulas.

Example-4-Step-3

Example #5 – Replace the Old Formatting of a Cell

When it comes to formatting of cells in Google sheets by copying from another cell, there are no options in the Find and Replace dialog box. TO copy the formatting from one cell to another, use the “Paint Format” option. Here, let us try to copy the formatting in cell A1 to B1.

Find-and-Replace-in-Google-Sheets-Example-5

Click on the cell format you want to copy. Click on the ‘Format Painter’ option and click on the destination cell (B1 in this case).

Find-and-Replace-in-Google-Sheets-Example-5-1

Example #6 – Find a Comment in a Worksheet

To insert a comment in a worksheet, select the cell and press Ctrl + Alt +M. A comment box appears.

Find-and-Replace-in-Google-Sheets-Example-6

Insert your comment in the comment box.

Find-and-Replace-in-Google-Sheets-Example-6-1

You can recognize a cell with a comment by a small triangle on the top right corner of the cell.

Find-and-Replace-in-Google-Sheets-Example-6-2

To search for a value in the comments, first choose the option “Show all Comments” in the top-right corner of your sheet.

Find-and-Replace-in-Google-Sheets-Example-6-3

In the Comments panel which appears on the right, you can search for values in your comments.

Find-and-Replace-in-Google-Sheets-Example-6-4

Important Things To Note

  1. Unlike other applications like Excel, you cannot copy-paste the formatting of one cell to another using the Find and Replace dialog box.
  2. Besides the Find and Replace dialog box, functions like SUBSTITUTE can be combined with other functions to Find and Replace multiple values in Google Sheets.
  3. If you encounter Find and Replace in Google Sheets not working, it could be because of several reasons. Check if options like “Match Case,” “Match Entire Cell Contents,” etc. haven’t been selected.

Frequently Asked Questions (FAQs)

1. What are the advantages of using Find and Replace in Google Sheets?

Besides the regular find and replace functions in Google Sheets, you can perform advanced find and search using regular expressions to search for values with a particular pattern. You can also search within formulas and perform case-sensitive searches option for searches within the same sheet or the entire workbook.

2. How to Find and Replace in Google Sheets within formulas?

To find a particular value and replace it in a Google sheet formula, go to the Edit-> Find and Replace option. From here, enter the values to find and replace, and choose the option “Also search within formulas” to Google Sheets replace text values in formulas.

FAQ-2

3. How to find and replace characters using Google Sheets formulas?

Besides using the Find and Replace dialog box, you can use many functions to replace characters in Google Sheets.
These include the Google Sheets SUBSTITUTE function which has the syntax SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number]) and searches for a specific text/character in the desired range and changes it. REGEXREPLACE is another function in Google Sheets to find and replace text.

Download Template

This article must help understand Find and Replace in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

Guide to What Is Find and Replace in Google Sheets. We explain how to use Find and Replace in Google Sheets with examples. You can learn more from the following articles. –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *