How to☝️ Find and Replace a Value in Google Sheets

To find and replace a value in Google Sheets, press the keyboard shortcut Ctrl + H for the PC or Cmd + Shift + H for the Mac. Next, enter the value you need to find and the value you wish to replace it with. Click Find then Replace or Replace all. Finally, click Done.

In this article, we will consider the various methods of how to find and replace any value in a Google Sheets document quickly and easily. Let’s check it out!

How to Find and Replace Values

The easiest method to find and replace a value is to use the keyboard shortcut Ctrl + H for PC or Cmd + Shift + H for Mac. In the dialog box that pops up, type the value you wish to find and the value you wish to replace it with. Click Find and then Replace or Replace all to make the change. Once completed, hit Done.

Scroll down to learn more methods to find and replace values!

Method 1: Using the Find and Replace Dialog Box

This method is the longform way to get to the Find and replace dialog box that comes up when you use the shortcut.

Difficulty: Beginner

Time Estimate: 10 Seconds

  1. Navigate to the Edit menu.
  2. Select the Find and replace option from the list.Using the Find and Replace Dialog Box
  3. Enter the value you need to find and the value you want to replace it with.
  4. Click Find to ensure the value can be found.
  5. Press Replace or Replace all.
  6. Finally, click Done.

The Find and replace option

And there you go! The replacements have been made.

The Data in Google Sheets

 Next, we will consider how we can use this function in more specific detail.

How to Find and Replace Match Case

The Match Case function allows you to search for specific text based on whether it is capitalized or not. This is useful if you want to ensure that the values you are searching for are replaced exactly as specified.

The Data

To find and replace a matching case, you need to open the Find and replace dialog box in one of the ways described above. Then type the values you need to change, check the Match Case box, click Replace, then select Done

How to Find and Replace Match Case

Easy as ABC! In this example, we found and replaced one value.

Data

How to Search Match Entire Cell Contents

Google Sheets’ Match entire cell contents function helps you find cells containing exactly the same content as the one you specify. This option can help you find each cell with a specific value and replace them all at once. 

The Data

To do this, open the Find and replace dialog box, type the value you want to change and click the check mark next to the Match entire cell contents option. Tap Replace all and then press Done.

How to Search Match Entire Cell Contents

Now all cells specifically matching the value you provided have been replaced.

Replaced values

How to Find and Replace Items Using Regular Expressions

In order to search for symbols, you can use the Search using regular expressions function. This can be useful for finding certain patterns in data or for more complex searches and replacing them.

The Data

To find and replace items using regular expressions, in the Find and replace box, add the item or symbol you need, then select Search using regular expressions, click Replace, and press Done.

Regular expressions allow you to search across multiple criteria at the same time. For instance, using “|” means that we’re looking either for “Regular Air 2” OR “Regular Air 4.”

How to Find and Replace Items Using Regular Expressions

Voila! In our example, this function helped to find and replace the multiple values specified.

Replaced Items

How to Search within Formulas

This option can be helpful if you want to find a particular value in a formula, or if you want to find all instances of a certain word or phrase within a formula. 

Dates

To search for values within formulas, open the Find and replace dialog box, type the formula, check the Also search within formulas box, press Find and Replace or Replace all, then click the Done button. 

How to Search within Formulas

Piece of cake!

The Dates

How to Find and Replace a Value Within a Link

The Also search within links function enables you to search for a specific keyword or phrase not only within the main body of text in a document but also within any hyperlinks that may be present. 

Links in Google Sheets

If your value is included within a link, just open Find and replace, add the values you need, and click the check mark next to the Also search within links. Hit Replace All, and then click Done.

How to Find and Replace a Value Within a Link

Easy-peasy!

Links

Method 2: Using Functions

Let’s take a look at another method that allows you to change values on your worksheet. This one involves using functions in Google Sheets.

The SUBSTITUTE Function

The SUBSTITUTE function in Google Sheets replaces one text string with another string. Check out how it works below.

Difficulty: Beginner

Time Estimate: 10 Seconds

  1. Click on the cell where you need the result.
  2. In the Formula bar, type the formula =SUBSTITUTE(A2:A13, “January”, “February”), where A2:A13 is the range with your data, January is the content you need to find, and February is the text that you will replace it with. 
  3. Press the Enter key on your keyboard.

The SUBSTITUTE Function

It’s that easy!

The REGEXREPLACE Function

REGEXREPLACE is a function that allows you to find and replace a text string using regular expression matching. This function allows you to search for text within a cell and replace it within a couple of seconds!

  1. Select a cell where you can enter the formula to replace values.
  2. Go to the Formula bar and enter the formula =REGEXREPLACE(A2:A13,”January”, “February”). Again, A2:A13 is the cell range, January and February are values you need to swap.
  3. Hit Enter.

The REGEXREPLACE Function

That’s all! Just like that, the change is made.

Find and Replace FAQs

If you have any more questions, see the Frequently Asked Questions section below.

Why is Find and Replace not working in Google Sheets?

There are some reasons why the Find and Replace function might not be working properly in Google Sheets:

  • Firstly, it could be that the function is case-sensitive, so you’ll need to make sure you’re using the correct capitalization for your search terms.
  • Secondly, check to see if the Find and Replace tool is set to look in the entire sheet or just at a certain range of cells. 
  • Lastly, if it’s set to look in a range of cells, make sure your search term is within that range.

In what way is the Find and Replace option useful?

The Find and Replace option is a great way to quickly find and replace specific text in a Google Sheets document. This can be helpful if you need to make changes to a large number of cells at once or if you want to find and replace text that appears in multiple cells.

More Google Sheets Tutorials
More Microsoft Excel Tutorials
Share This Post
Daniel Smith
Daniel Smith
Daniel Smith is automation consultant with a passion for technology, data, AI, and machine learning.

The Spreadsheet Daddy add-on rebrands to Synterrix. Click here to learn more.

X