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 and replacing 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.

database for using keyboard shortcut to perform search and replace tool

Note: Actually, there are two forms of shortcuts, and you can choose either of them based on your needs. Both shortcuts have been illustrated below for the benefit of the reader.

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.

find and replace short menu

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

google sheets search and replace short menu with options

  • 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.

google sheets how to search and replace detailed menu

  • 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.

how to perform find and replace in google sheets

  • 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.

drop down search menu in find and replace menu

  • 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.

match criteria in google sheets

  • 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.

how to active replace command in find and replace tool in google sheets

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

notification panel in find and replace tool in google sheets

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.

output of using keyboard shortcut in google sheets to perform search and replace


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.

data for search and replace in google sheets

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.

navigation through the toolbar to find and replace

  • 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.

steps to follow to perform search and replace in google sheets

  • 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.

google sheets notification inside find and replace

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

find and replace output


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.

five different match criteria in google sheets find and replace toolI. 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.

match case

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.

match entire cell contents

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.

search using regular expression

Regular Expression Breakdown:

  • ^C: text beginning with capital C.
  • [a-z…+$: text ends with any alphabet.

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.

search within formulae

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.

search and replace within formulae

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

notification of result in case of search within formulae

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

result of search and replace within formulas

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


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.

database for substitute function

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“.

how to add new column in google sheets

  • 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.
=SUBSTITUTE(C6,"Planning","Development")

application of substitute function as search and replace

Formula Breakdown:

  • C12 specifies the location of the search within the spreadsheet.
  • “Planning” is the value or text to search within the cell.
  • The replacement value is “Development”.
  • Press ENTER to get the result.

result from substitute function in search and replace

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.

data for replace function

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.
=REPLACE (F6,1,7,"XXX-XXX")

use of replace function in google sheets to perform search and replace

Formula Breakdown:

  • F6 denotes the text or the part which will be replaced.
  • 1 will serve as the starting point for the replacement.
  • 7 will be the number of characters that must be changed.
  • The text that will be put into the original text is “XXX-XXX”.
  • To obtain the outcome finally, press ENTER.
  • The remaining cells in the column will automatically fill out simultaneously.

autofill of column in google sheets

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

result derived from the replace function in google sheets

Note: You can hide the Office Contact column on this occasion and present only the Contact column.

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“.

data for regexreplace function in google sheets

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.
=REGEXREPLACE (C6,"Engineer","Consultant")

use of regexreplace function in google sheets

Formula Breakdown:

  • Text that will be replaced in whole or in part is marked with a C6.
  • Engineer” refers to All matching instances in the text that will be replaced.
  • Consultant” as the replacement value.
  • Afterward, press ENTER.
  • The remaining column cells will be filled out automatically in the meantime.

autofill after using function to search and replace in google sheets

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

result of regexreplace function in google sheets


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.

Ishrak Khan

Ishrak Khan

Ishrak Khan is a writer and urban planner born in Dhaka, Bangladesh, one of the largest mega-cities. He is a professional Content Developer closely working with OfficeWheel. He simply likes to enthrall his readers. He loves Football, Nico Robin, Deserts, and Twix. If you like the post, give him a heart on Instagram.

We will be happy to hear your thoughts

Leave a reply

OfficeWheel
Logo