Find and Replace In Google Sheets (All You Need to Know)

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 Google Sheets Find and Replace feature to quickly find what you need and replace it with exactly what you want.

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 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:

Data for using Find and Replace in Google Sheets

How to Use Find and Replace In Google Sheets

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.

Find and Replace dialog box

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

Find in sheet option in Google sheets

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

Next and Previous option in Google Sheets

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:

Data for using Find and Replace in Google Sheets

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.Finding and Replacing All Instances of a Search String at Once
  4. You will see a notification that tells you how many instances of the search string were found and replaced.Instances replaced by find and replace
  5. Click Done.

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

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.

Select this sheet in drop down

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.Find Instances of a Search String One by One
  5. This will mark the location of the search string in your worksheet and you should now see the Replace button activatedClick on Replace
  6. Now enter ‘Operations-1’ in the input box next to ‘Replace With’.
  7. Click on the Replace button.Enter the text to replace with
  8. The first occurrence of the word Operations should get replaced with the word Operations-1.Replaces one word with another
  9. At the same time, the next occurrence of the word Operations should get selected.Second instance of the word is selected
  10. Enter ‘Operations-2’ in the input box next to ‘Replace With’.
  11. Click on the Replace button again.Enter the second instance of the replacement text
  12. This time the second occurrence of the word Operations should get replaced with Operations-2.Replaces second instance of the word
  13. Click Done.

This is what the worksheet should look like now:

Both words replaced

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.Matching Entire Cell Contents
  5. You will see a notification that tells you how many instances of the search string were found and replaced.Replaced Entire Cell Contents
  6. Click Done.

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

Replaced Entire Cell Contents name

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.

Select Match case checkbox

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:

Metacharacter What it Represents
^ The beginning of the string
$ The end of the string
. A single character
? Zero or one occurrence of a character or string
* Zero or more occurrences of a character or string
+ One or more occurrences of a character or string
| The Or operator
() This holds a group of metacharacters inside it and represents that sequence of characters
[] This holds a set of characters and represents any one of the characters inside it
[^] This holds a set of characters and represents any one of the characters not listed inside it
\ This is used to escape a special character

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.Find and Replace Regex
  7. You will see a notification that tells you how many instances of the search string were found and replaced.replaced 3 instances of words strating with P
  8. Click Done.

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

Name replaced with 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”.

Formula to add the wrd Only to the numbers

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.Search within foirmulas
  5. You will see a notification that tells you how many instances of the search string were found and replaced.Replaced 7 instances of the word only
  6. Click Done.

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

word replaced from within the formula

We discussed different strategies in this tutorial, to help you find what you need quickly and effectively so that you can subsequently replace it with what you want.

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

Leave a Comment