How to Search and Replace in Google Sheets (2 Easy Ways)

Working with large datasets often requires a change within the sheet and formulas. As a matter of fact, Finding and replacing texts, numbers, and formulas can be a rather simple process if the spreadsheet isn’t too big. However, if it is known how to search and replace in google sheets, it makes the workload easy. This article will help you determine the appropriate technique to search and replace data in Google Sheets so that you don’t waste time looking through irrelevant results.

A Sample of Practice Spreadsheet

You can copy the spreadsheet that we’ve used to prepare this article.

2 Ways to Search And Replace in Google Sheets

Thankfully, Google Sheets has a number of methods for finding texts, characters, values, and formulas inside a spreadsheet. In this post, we’ll go through each of them.

1. Using Find and Replace Tool

You will see how to use the find and replace tool and all of its functions in this section. There are so many applications for it. Each is explained with a suitable example.

1.1 Using Keyboard Shortcut

Undoubtedly, this is the quickest way to find and replace data. You only need to follow a few simple steps to finish your task. With the appropriate examples, check the explanation of the method below.

Assume you work for HENSEL PHELPS as HR. A dataset of your current employees is available. Recently, they relocated their Southeast office to Midsouth. Hence, an update in the branch column seems necessary for this purpose. To finish the work straightaway, we’ll employ the steps listed below.

Steps:

• To open the Find and Replace menu, Use the keyboard shortcut CTRL+F (on a Windows PC) or CMD+F (on a Mac).
• Along with the formula tab at the top of the table, a little menu will show up.

• There are three dots right next to the “Find in sheet“. For more options, click it. A detailed menu will pop up.

• To be quick, simply pressing CTRL+H (on a Windows PC) or CMD+SHIFT+H (on a Mac) will open the detailed menu in a new window.

• At this point, you will now enter the value or text you are looking for in the box next to Find. It’s Southeast in this case.
• On the other hand, we will type the text that will replace it in the Replace with box, which in this case is Midsouth.

• You will find a drop-down Search menu. Depending on the dataset you can set the range of this tool. There are 3 options. For this case particularly, we are going to use This sheet only.

• The next section lists 5 different categories of search criteria. Each has a special role of its own. We’re looking for cells that exactly match the text in the Find box. As a result, we’ll choose to check “✔” the second choice “Match entire cell contents”.
• Clicking on Find will make the feature cycle through all the matched instances.

• However, the Replace option doesn’t appear until after the preceding step. Thereafter, the Replace command becomes available. Now, select Replace to change each item individually or Replace all to replace them all at once.

• Whichever option you select, you will be presented with a notification when the task is complete.
• Click Done to see the result.

Thereupon, you can see that the Branch column contains no cells with the word Southeast; instead, all of them are replaced with the word Midsouth.

1.2 Navigating from the Edit Option

You should also probably change the Office Contact column. We’ll walk you through the process while using the Edit option.

Steps:

• Click the Edit button first, which is located at the top of the toolbar.
• Search for Find and Replace at the bottom of the little window that will pop up. The Detailed find and replace menu will appear once you click it.

• We will now locate the number 407-856-2400 and substitute 615-338-9104 for it. Therefore, enter 407-856-2400 in the Find bar.
• In the Replace bar, type 615-338-9104.
• Select Specific Range from the Search drop-down menu, and a new bar will appear next to it.
• For this example, choose the range from F6 to F13.
• Then again, select “✔” for the second option “Match entire cell contents”.
• To start the search, click Find.

• Depending on your preference, you can select Replace or Replace all. Once all the changes have been done, a notification will appear at the bottom.
• To close the window and view the outcome, click Done.

The Office Contact column now lists the new numbers for the Midsouth branch evidently.

1.3 Searching with Different Criteria

The find and replace function in Google Sheets offers five different search choices. In the two cases above, we’ve simply presented the Second choice. To help you comprehend these parameters, we’ll give a brief explanation here.

I. Match Case
Check this box to acquire all the relevant outcomes if you want to search for specific words in either uppercase or lowercase. It will check each cell for that value. We have highlighted two different cases in the image, this is to say, below that meet the search parameters. All cells featuring the word “Engineer” will be highlighted, nevertheless.

II. Match Entire Cell Contents
Users can use this option to look for cells that have the exact same information as what was entered in the Find box. In earlier examples, we have already demonstrated how to use this case.

III. Search Using Regular Expressions
This will look for spreadsheet cells that match the pattern you searched for. You must develop a certain syntax, that is, special characters and construction guidelines, to produce a regular expression.
In this example, we will use the expression “^C[a-zA-Z]+$” to find names that begin with the letter C.

IV. Search within Formulas
By selecting this, Sheets will ensure that it looks for outcomes specifically inside of formulas as well. Check out the example below.

Assume that employees at the company HENSEL PHELPS will get a festival bonus equal to half of their base pay. However, they decided to provide gifts and a 30% bonus at a recent meeting. Consequently, it is necessary to update the database. Given that, you may see the formula $D13*50% by looking at cell E13. We will just change the 50% to 30%, and we will use the syntax &” + Gifts” to denote the gifts.

Steps:

• Open the search and replace menu using the keyboard shortcut CTRL+H (on a Windows PC) or CMD+SHIFT+H (on a Mac).
• In the Find bar, enter 50%.
• Secondly, input 30%&”+ Gifts” in the Replace bar.
• A new bar will display next to the search drop-down option, choose Specific Range.
• Select the range E6:E13 for this example.
• Check “✔” the fourth option, “Also search within formulae,”
• Click Find to start the search.
• If you want the work to be done right away, click Replace or Replace all.

• As is customary, a notification will appear to display the outcomes.
• Click Done to finalize.

Obviously, you will now notice the change if you check any cell, in this case, E13 in the Festival Bonus 50% column.

V. Search within Links
In short, this will search for the word or value inside the hyperlink you added to the spreadsheet.

Read More: How to Search in All Sheets in Google Sheets (An Easy Guide)

2. Using Functions to Search and Replace

There are some built-in functions in google sheets which does the work of search and replace. For the convenience of the readers, we tried to provide real-world examples.

2.1 Using SUBSTITUTE Function

To show how to use the SUBSTITUTE function, we will use the dataset below. Assume that HENSEL PHELPS has chosen to rename its Planning department as the Development Department. The job postings thus require an update as well. To finish the update, adhere to the steps below.

Steps:

• We must add a new column. Right-click in the C:C cell or C column number to open the options menu.
• Now choose “+ Insert 1 column to the right“.

• Give the new column a header. It is Update in this instance.
• To obtain the update, we will employ the formula below in cell D6.

• Press ENTER to get the result.

You’ll see that the term Development replaced the word Planning at the bottom of the Update column without delay, giving the position a new title of Development Director and Development Manager likewise.

2.2 Application of REPLACE Function

Consider a scenario in which the authority decided to hide the first six digits of their Office Contact number. In this instance, we’ll make use of the REPLACE function. These are the procedures.

Steps:

• We must once more create a new column this time next to the Office Contact field.
• In a word, put a header up there, like Contact.
• In cell F6, we’ll apply the formula below.

• To obtain the outcome finally, press ENTER.
• The remaining cells in the column will automatically fill out simultaneously.

To put it another way, you can now see that preserving the last four numbers, the initial digits are now written as XXX-XXX.

2.3 Use of REGEXREPLACE Function

In this context, there are two uses for the function REGEXREPLACE. The REGEXREPLACE function supports the use of both text strings and regular expressions. We’ll provide a description for just one use case—text strings. Assume that the word “Engineer” will be swapped out for “Consultant“.

Steps:

• To the right of the Designation column, we must once more add a new column in the first place.
• Secondly, create a header; in this instance, the header is called New Post.
• Thirdly, in cell D6, we’ll apply the formula.

• Afterward, press ENTER.
• The remaining column cells will be filled out automatically in the meantime.

You’ll notice in the final analysis that the word “Consultant” has replaced every instance of “Engineer” in the new post column.

Read More: 

Final Words

In sum, this is all you need to know about how to use the search and replace in Google Sheets. Moreover, it’s a quite useful tool to use. Practice is the key to mastering this tool. Use our spreadsheet as a resource. For more information, visit OfficeWheel on the web.

Related Articles

• How to Search for Duplicates in Google Sheets (3 Quick Ways)
• Create a Search Box in Google Sheets (4 Easy Methods)
• How to Search in a Column in Google Sheets (3 Suitable Ways)