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
- Navigate to the Edit menu.
- Select the Find and replace option from the list.
- Enter the value you need to find and the value you want to replace it with.
- Click Find to ensure the value can be found.
- Press Replace or Replace all.
- Finally, click Done.
And there you go! The replacements have been made.
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.
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.
Easy as ABC! In this example, we found and replaced one value.
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.
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.
Now all cells specifically matching the value you provided have been replaced.
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.
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.”
Voila! In our example, this function helped to find and replace the multiple values specified.
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.
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.
Piece of cake!
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.
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.
Easy-peasy!
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
- Click on the cell where you need the result.
- 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.
- Press the Enter key on your keyboard.
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!
- Select a cell where you can enter the formula to replace values.
- 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.
- Hit Enter.
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.
