Find and Replace in Google Sheets (3 Ways)

Find and Replace is an indispensable tool provided by Google Sheets. If you are thinking of the simple Find option, then you couldn’t be further away from the truth.

The Find and Replace option in Google Sheets caters to all levels of spreadsheet users, incorporating regular expressions and formulas.

On that note, let’s see what Find and Replace has on offer for us and understand the many ways we can utilize it for our spreadsheets.

Why do we need Find and Replace?

Google Sheets’ Find and Replace function looks through the entire worksheet for your specific word and replaces it with any value (even blanks).

The Find and Replace dialog box offers more than just finding and replacing. It presents us with multiple criteria for our searches, even some niche or complex ones like finding with regular expressions and within formulas.

To list some of the conveniences down:

  • You can make case-sensitive searches.
  • Helps look for and within formulas.
  • You can search with regular expressions for strings that follow a pattern.
  • You can use wildcards to enhance your searches.

find and replace window in google sheets

Read More: Find and Replace with Wildcard in Google Sheets

3 Ways to Find and Replace in Google Sheets

1. Find and Replace In a Browser

We have created this small worksheet to show you the various uses and aspects of the Find and Replace function of Google Sheets. We are running it on a Chrome-based web browser.

base table for find and replace in google sheets

Before we dive into the Find and Replace option, if your worksheet/dataset is small and simple, it is always better to use the Find option to get your works done.

Keyboard Shortcut: CTRL+F (for PC) or CMD+F (for Mac)

We are looking for the word “shirt”. As you can see, it highlights all the instances of the word “shirt”. You can replace the ones that you choose to manually.

But for a more in-depth search and replace option we will utilize the Find and Replace function of Google Sheets.

The natural way to get to the Find and Replace dialog box is by navigating through the Edit tab from the Toolbar menu.

Edit > Find and Replace

navigating to find and replace from the edit tab

Or you can simply use the keyboard shortcuts:

  • For PC: CTRL+H
  • For Mac: CMD+SHIFT+H

the find and replace window in google sheets

Read More: How to Use Find and Replace in Column in Google Sheets

a. All Instances

As we have just seen with Find, we will be trying to replace the word “shirt” with something else, like “pant”. Let’s see how it goes.

using the find feature from the find and replace window

As we click on Find, the function will cycle through every instance of “shirt” in the worksheet with every click. Once it is done cycling, it will notify you that the loop is complete.

At any point during this cycle, you can click on the Replace button to replace the instance with your desired word, “pant”.

But if you want to replace all of the instances, simply click the Replace all button.

message showing all the instances have been found

The Replace all result:

Result for find and replace all in google sheets

Note: By default, All Sheets will be selected in the Find and Replace dialog box. You can change this option anytime.

All Sheets option selected by default

b. Match Case

From this point onward, we will be using the conditional options available to us in the Find and Replace dialog box, starting with the Match case option.

These are the conditions we have applied:

conditions applied for find and replace in google sheets

We will be replacing all instances of “shirt” with “pant”, but it will be case-sensitive as determined by checking the Match case option. Click Replace all.

Our result:

our find and replace results with new conditions

As you can see, the two instances of “Shirts” have not been replaced since the S” is in caps.

c. Match Entire Cell Contents

The next option we will be looking at is the Match entire cell contents option. Checking this option will tell the Find and Replace option to make a character-for-character match in an individual cell.

Let’s show an example:

Similar to our previous example, we will try to find the word “shirt” in our worksheet and replace it with “Redacted”, and this time, we will check the Match entire cell contents option. Let’s see what happens:

find and replace conditions for match entire cell contents

The result:

no matches found to replace

It shows No matches found because none of our cells contain just the word “shirt” individually in a cell.

Now, if we change the Find word to “shirts” however:

results for find and replace for shirts in google sheets

Read More: Find All Cells With Value in Google Sheets (An Easy Guide)

Similar Readings

d. Matching with Regular Expressions

A unique way to approach Find is by using Regular Expressions. These are special symbols, often known as “metacharacters” that have their own functionality within Google Sheets.

^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

With the help of these regular expressions, we can customize our Find searches down to match each character.

Let’s now look at an example where we will find and replace a string that starts with a “Tr” and ends with an “s”. We will keep our search case-sensitive and will only be implemented in the Shipment column.

Our regular expression for this condition will be:

^Tr[a-zA-Z ]+s$

Formula Breakdown

  • ^Tr: We look for the characters “Tr” at the beginning of the string
  • [a-zA-Z ]+: All alphabetical characters, both small and caps, are included
  • s$: Our string must end with an “s”

Make sure the Search using regular expressions option is checked. Otherwise, the formula will not work.

search using regular expressions

Our result:

Result of find and replace in google sheets using regular expressions

Read More: How to Search in Google Spreadsheet (5 Easy Ways)

e. Search Within Formula

For our final example, we will be searching for words within formulas.

Our Special ID column contains a very simple formula:


cell containing formula

To show the example, we will find the string “XP” and replace it with “REP”. Our range will be the Special ID column. And make sure to check the Also search within formulas option.

also search within formulas condition

Our result:

find and replace in google sheets within formulas

Read More: How to Find the Range in Google Sheets (with Quick Steps)

2. Find and Replace In a Mobile Device

You can also use the Find and Replace function in Google Sheets’ mobile application, for both Android and Apple devices. Simply follow these steps:

Step 1: On the top-right corner of the screen, you will see an icon with 3 dots. Tap and hold to bring up the menu.

navigating to options in google sheets from a mobile device

Step 2: The Find and Replace option should be at the top of the menu. Tap on it.

find and replace option in google sheets from a mobile device

Step 3: As per usual, type in your search (at the top of the screen) and what to replace it with (at the bottom of the screen).

adding conditions to find and replace from a mobile device

And you are done!

As you may have noticed, the mobile version of the Find and Replace function is not as elaborate as the browser one. So if you have to make replacements based on complex conditions, the browser is the way to go.

3. Formula to Find and Replace in Another Cell

If you do not want to replace strings in a cell as a whole, Google Sheets provides us with a couple of functions to take the string from one cell and replace it with whatever modifications we desire and put it in another cell. These are:


We will be using the following simple table to show the examples.

table to find and replace using formulas

a. SUBSTITUTE Function

This is a simple substitute function that takes a cell reference, looks for a designated string and replaces that string with a user-defined string. It is case-sensitive.

The SUBSTITUTE function syntax:

SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])

SUBSTITUTE function syntax

Our example is to find the string “XP” and replace it with “REP” In the range.

Our formula:


using substitute to find and replace

Read More: Find Value in a Range in Google Sheets (3 Easy Ways)


This function follows the same fundamentals as SUBSTITUTE, only this time we have to build a regular expression for our condition.

The REGEXREPLACE function syntax:

REGEXREPLACE(text, regular_expression, replacement)

the REGEXREPLACE function syntax

Our example conditions are the same: replace all strings that end with “XP” with “REP”.

Our formula:


using REGEXREPLACE function to find and replace

For our formulas, we have kept things simple. But know that much more complex conditions can be applied, especially with REGEXREPLACE.

Read More: Use REGEXREPLACE to Replace Multiple Values in Google Sheets (An Easy Guide)

Final Words

Congratulations, you have finally made it to the end of the article. Considering our topic of discussion, it does seem like a long ride. But from our journey, we have understood that Find and Replace in Google Sheets can be used at great depth and can match any user’s needs.

Please feel free to leave any queries or advice you might have in the comments below.

Related Articles

Mehrab Imtiaz

Mehrab Imtiaz

Mehrab Imtiaz is a Technical Writer for specializing in everything Google Sheets. Mehrab has always had a profound passion for writing and with his experience with MS Excel and Google Sheets throughout his career, it seems to be a great match when it comes to dishing out tips and tutorials for all to enjoy.

We will be happy to hear your thoughts

Leave a reply