9 Ways to Use Google Sheets Find and Replace (All Easy)

Google Sheets is known for having a number of convenient and time-saving features. One such feature is the option to find and replace text. Finding and replacing text within a large spreadsheet is a kind of art.

To ensure that you spend less time sifting through irrelevant results, you should be able to make laser-sharp searches to find exactly what you need.

In this tutorial, we will show you how you can make effective use of the Find and Replace Google Sheets feature to quickly find what you need and replace it with exactly what you want. But you can boil it down to going to Edit > Find and replace and following the prompts.

Why the Google Sheets Find and Replace is Useful

The ‘Find and Replace’ feature in Google Sheets lets you search through entire workbooks for specific values and replace them with whatever you need.

The great thing about the Find and Replace feature is that it gives you the option for Google Sheets to replace all instances of your search string at once or to replace them one by one.

Here are some other conveniences you get from Google’s Find and Replace:

• It lets you use regular expressions to search for values that follow a particular pattern.
• It lets you use wildcards to search for values similar to your search string.
• It lets you search in formulae and in the results of formulae.
• You have the option to search for exact cell matches or cells containing the search string.
• You have the option to specify whether or not you want to make the search case-sensitive.

We will take a look at the above applications by going through examples. We will use an imaginary dataset (shown below) to apply our searches:

How to Use the Find and Replace Google Sheets Feature

There are three ways to use Find and Replace in Google Sheets:

1. Navigating to Edit->Find and Replace
2. Using the keyboard shortcut CTRL+H (on a Windows PC) or CMD+SHIFT+H (on a Mac)
3. Using the keyboard shortcut CTRL+F (on a Windows PC) or CMD+F (on a Mac)

There’s a difference between the first and second shortcut methods mentioned here. When you press CTRL+H you get the Google Sheets Find and Replace dialog box.

Pressing CTRL+F, on the other hand, opens a generic Find text field that you can use anytime on your computer.

There’s another small, but often useful difference. The Google Sheets Find and Replace dialog box lets you find your search string one instance at a time.

So when you press the Find button in the dialog box, you can only see the first occurrence of the search string in your sheet.

To see the next occurrence, you need to press the Find Next button. So it doesn’t really provide you with a way of knowing how many instances of the string there are until you actually replace all.

If you use the generic transpose Google SheetsFind box, on the other hand, you can see all instances of the search string highlighted, which you can then navigate through one by one, using the up and down arrows.

Using the Google Sheets Find and Replace Dialog Box

The Find and Replace dialog box gives you a number of options to fine-tune your search and replace found instances of your search string.

Let us go over each of the options one by one using different search examples.

Finding and Replacing All Instances of a Search String at Once

Here’s the worksheet we want to work on once again:

Let us say you want to replace all instances of the search string ‘Manufacturing’ with the string ‘Production’.

1. Enter the following inputs in the Find and Replace dialog box:
   • Find: Manufacturing
   • Replace with: Production
   • Search: This Sheet

2. At this point, you can leave all the other inputs blank or unchecked.
3. Click on the Replace All button.
   

   

4. You will see a notification that tells you how many instances of the search string were found and replaced.
   

   

5. Click Done.

You should now see all instances of the word ‘Manufacturing’ replaced with the word ‘Production’.

Note: The Find and Replace dialog box lets you find what you need in the current sheet, all the sheets, or even a selected range of cells. Just select the drop-down arrow list next to “Search”, and select your required option.

Replacing Instances of a Search String One by One

Now say you want to replace the string ‘Operations’ with different Operations department numbers, like Operations-1 and Operations-2.

In such cases, it would not make sense to replace all instances of the word ‘Operations’ at once. To replace instances of the search string one by one, follow these steps:

1. Navigate to Edit->Find and Replace.
2. Enter the following inputs in the Find and Replace dialog box.
   1. Find: Operations
   2. Search: This Sheet

3. At this point, you can leave all the other inputs blank or unchecked.
4. Click on the Find button.
   

   

5. This will mark the location of the search string in your worksheet and you should now see the Replace button activated
   

   

6. Now enter ‘Operations-1’ in the input box next to ‘Replace With’.
7. Click on the Replace button.
   

   

8. The first occurrence of the word Operations should get replaced with the word Operations-1.
   

   

9. At the same time, the next occurrence of the word Operations should get selected.
   

   

10. Enter ‘Operations-2’ in the input box next to ‘Replace With’.
11. Click on the Replace button again.
    

    

12. This time the second occurrence of the word Operations should get replaced with Operations-2.
    

    

13. Click Done.

This is what the worksheet should look like now:

Matching Entire Cell Contents

Now, what if you want to replace all instances of the name ‘Paul Lee’ with the name ‘Paula Lee?’

Notice if you search for the name ‘Paul’, Find and Replace considers both the names ‘Paul Lee’ and ‘Paul Rodriguez’.

However, if you’re looking to replace only instances where the name ‘Paul Lee’ appears, and not ‘Paul Rodriguez’, then you will need to do the following:

1. Navigate to Edit->Find and Replace.
2. Enter the following inputs in the Find and Replace dialog box.
   1. Find: Paul Lee
   2. Replace with: Paula Lee
   3. Search: This Sheet

3. Check the checkbox next to the ‘Match entire contents’ option.
4. Click on the Replace All button.
   

   

5. You will see a notification that tells you how many instances of the search string were found and replaced.
   

   

6. Click Done.

You should find all instances of the name ‘Paul Lee’ replaced with the name ‘Paula Lee’.

Note: if you also want to make your search case-sensitive, then you will need to check the checkbox next to the Match Case option.

Matching Regular Expressions

A great option you get with the Find and Replace dialog box is the ability to use regular expressions (Regex) to fine-tune your search.

Using this, you can search for strings that match a particular pattern, like an ID format, an email address, or a URL.

What is a Regular Expression?

A regular expression is a sequence of characters, including symbols, letters, and numbers that are combined to form a search string.

A regular expression is often characterized by certain special symbols, also known as ‘metacharacters’. Here are some examples of metacharacters that you can use:

Search and Replace Text Strings Starting with a Specific Alphabet

Suppose you want to find all names that start with a ‘P’.

Let’s say you want to replace the names with the word ‘classified’. In that case, you can use the regular expression ^P[a-zA-Z ]+$ to find all names that start with a P.

Note that the symbols “[a-zA-Z ].

Here are the steps you need to follow to use this regular expression in the Find and Replace dialog box:

1. Select the Name column (range A2:A8).
2. Navigate to Edit->Find and Replace
3. Enter the following inputs:
   1. Find: ^P[a-zA-Z ]+$
   2. Replace with: classified
   3. Search: Specific Range

4. Check the checkbox next to the ‘Search using regular expressions’ option.
5. You will notice that the check box next to the ‘Match case’ option also gets checked automatically.
6. Click on the Replace All button.
   

   

7. You will see a notification that tells you how many instances of the search string were found and replaced.
   

   

8. Click Done.

You should find all names that start with a ‘P’ replaced with the word ‘classified’.

Searching within Formulae

In general, Find and Replace just searches through cell contents and results of formulae. If you also want to see if your search word is contained within the actual formula of cells, then there’s an option for that too.

In our example, if you take a look at the contents of column E, you will notice that we used a formula to calculate the Total and then displayed it along with the string “only”.

Let’s say you want to remove the word “only” from all the formulae. For this, you will need to follow the steps below:

1. Navigate to Edit->Find and Replace
2. Enter the following inputs in the Find and Replace dialog box (since we want to remove the word “only”, we simply leave the ‘Replace with’ field blank:
   1. Find: only
   2. Replace with:
   3. Search: This Sheet

3. Check the checkbox next to the ‘Also search within formulae’ option.
4. Click on the Replace All button.
   

   

5. You will see a notification that tells you how many instances of the search string were found and replaced.
   

   

6. Click Done.

You should find all occurrences of the word “only” removed from the formulae (and thereby the results of the formulae).

Using the SUBSTITUTE Formula

You can also use functions to have Google spreadsheets replace text. The SUBSTITUTE function is a Google Sheet replace function used to find and replace text in one or all instances in Google Sheets.

The formula has the following Syntax:

=SUBSTITUTE(text_to_search, search_for, replace_with, number_of_occurrences)

Text_to_search: the text string where the search will be performed

Search_for: the text or value that is being searched.

Replace_with: the text or value that is replacing the search_for.

Number_of_occurrences: The number of times search_for appears within text_to_search to replace with replace_with.

In our example sheet, we can replace the word Production with Operations using the SUBSTITUTE function.

In this case, we would use the Google Sheets find and replace formula:

=SUBSTITUTE(B2,”Production”,”Operations”)

If you want to leave an empty space instead, you can leave the quotation marks for replace_with empty.

One thing to note about the SUBSTITUTE function is that it only works with texts. If you want Google Sheets to replace numerical values, then you may need to combine the VALUE function with the SUBSTITUTE function.

Using the REGEXREPLACE Function

Another replace Google Sheets function that you can use to find and replace in sheets is the REGEXREPLACE Function.

This function uses the Syntax:

REGEXREPLACE(text, regular_expression, replacement)

text – The text, a part of which will be replaced.

regular_expression – The regular expression. All matching instances in the text will be replaced.

replacement – The text which will be inserted into the original text.

Using this function in our example sheet, we can replace the word Production with Manufacturing.

In this case, we would use the formula:

=REGEXREPLACE(B2,”Production”,”Manufacturing”)

Like the SUBSTITUTE function, this function also only works to find and replace text values. If you have numerical values, then you will need to combine them with the VALUE function.

How to Replace Cell Reference in a Formula

Sometimes, you need to edit your formulas in Google Sheets. You can replace the cell references in a formula either manually or by reselecting.

In our example above, we can replace the cell reference in the formula by:

1. Double-click the cell with the formula
2. Delete the cell reference that is there
3. Select the new cells you want to use in your formula

Your formula will update the results with the new cell reference. You can also type in the cell reference manually, but this method is simpler and eliminates errors.

Conclusion

We discussed different strategies for how to replace in Google Sheets to help you find what you need quickly and effectively so that you can subsequently replace it with what you want. If you’re still having trouble understanding, then we recommend you try out a comprehensive Google Sheets course to brush up on your overall skills.

The Google Sheets Find and Replace dialog box is a highly valuable tool. It provides you a number of options, including the option to replace some or all occurrences of a search string, to look for strings that follow a pattern, and to also look inside formulae.

The best way to master the find and replace Google Sheets tool is through practice. With time you will find yourself getting better and better in devising just the right search string to perform your find and replace with ease and accuracy.

If you found this article useful, you can also check out how to remove the first character in Google Sheets.

Related:

• How to Fill Empty Cells with 0 in Google Sheets
• Find and Replace in Google Docs
• Count Cells IF NOT Blank (Non-Empty cells) in Google Sheets
• How to Count Cells with Specific Text In Google Sheets
• How To Remove Duplicates In Google Sheets
• How to Transpose Data in Google Sheets
• How to Change Text Case in Google Sheets (Upper, Lower, or Proper)