How to Find and Replace in Google Sheets

When you’re dealing with a lot of large spreadsheets regularly, you may need a way to find specific data sets within a sheet and replace them with newer values. Finding and replacing texts, values, and formulas manually can be a relatively simple affair if the spreadsheet isn’t that large. But the same can become quite tiring if you have a bunch of data scattered across the spreadsheet.

Fortunately, Google Sheets offers various ways you can find and replace texts, characters, values, and formulas inside a spreadsheet and in this post, we’ll explain all of them.

How to find and replace in Google Sheets

There are four ways you can find and replace characters in Google Sheets. We’ll explain how to use every one of them below.

Method 1: Using the Find and Replace tool

Before we move on to more complex functions, if you wish to substitute texts and values inside a spreadsheet, you don’t have to look anywhere other than Google Sheets’ Find and Replace tool.

To access the Find and Replace tool, use the following keyboard shortcuts:
On Windows: CTRL + H
On Mac: CMD + Shift + H
If you don’t fancy these keyboard shortcuts, you can open the Find and Replace tool inside Google Sheets by clicking on the Edit tab from the top toolbar and selecting Find and replace from the dropdown menu.
When the Find and replace dialog appears inside Sheets, you’ll be able to configure what you’re searching for and what you want to replace the results with.
To get started, in the Find text box, type a word, value, or character you want to find inside the spreadsheet, and then click the Find button at the bottom.
Now, select where you wish to search for the specific word or value from the Search dropdown box. You’ll be able to choose one of these three options:

All sheets: Selecting this option will find your word or values across all spreadsheets in the file you have opened.
This sheet: Selecting this will search for texts present in the sheet that’s currently open.
Specific range: When you select this, you can specify a cell range from which Sheets will search for your desired text.
Sheets will now highlight the calls that contain the specific word or value.
If you want to replace one of the results currently highlighted with another set of words or values, type it inside the Replace with text box and click on Replace at the bottom of the dialog.
If you want to replace all of the results with some other words and values, you can click on Replace all instead.
Sheets will notify you about the changes made within the Find and replace tool.
Additionally, you can use the checkboxes below to search for words and values with more precision.

Match case: If you want to search for specific words with an uppercase or lowercase, you can check this box to get the desired results.
Match entire cell contents: This option lets users search for cells with the exact same content that you typed in the Find box.
Search using regular expressions: This will search for cells in your spreadsheet that follow the pattern that you searched for.
Also search within formulas: This will make sure Sheets also searches for results inside a formula.
Also search within links: This will search for the word or value inside the link you added to the spreadsheet.
When you’re done using the Find and replace tool, click on Done to close it and return to your spreadsheet.

Method 2: Using SUBSTITUTE function

If you don’t prefer using the Find and Replace tool but want to get things done using formulas and functions, you can use the SUBSTITUTE function that replaces an existing text in a spreadsheet with new text. The syntax for this function goes like this – SUBSTITUTE(text to search, search for, replace with, occurrence number), and here are what parameters you need to add inside the brackets “()”.

text to search: The cell you want to search for and replace from the spreadsheet.
search for: Enter the text string you want to search for within the above parameter.
replace with: The text you want to replace your selection with.
occurrence number: If you have multiple instances of “search for” to replace texts with and you want to replace only a specific result, you can provide the occurrence number here so that only this particular occurrence is replaced by the new text.

To get started, select the cell where you wish to add the function and updated text.

In this example, we try to replace the word “Project” with “Campaign” from column C, so we click on cell D4 to get started with the first replacement.
Inside this cell, type =SUBSTITUTE(C4,”Project”,”Campaign”) and then press the Enter key.
Sheets will now show you the result inside cell D4 along with relevant suggestions for all the other cells in column D. If you’re satisfied with these suggestions, click on the tick mark inside the Suggestions autofill box.
If this box doesn’t show up, don’t fret. You can copy the formula across other cells in the column by clicking the square blue icon from cell D4 and dragging it downwards.
You should now see the substituted words or values in the cells under column D. Depending on the results, you can edit or save changes to the spreadsheet.

Method 3: Using the REPLACE function

An alternative to the SUBSTITUTE function is the REPLACE function which allows users to replace part of a text string with a different text string. The syntax for this function goes like this – REPLACE(text, position, length, new text), so in a way, this function works in similar ways to that of the SUBSTITUTE function. When using this function, here are the parameters you have to add inside the brackets “()”:

text: The text whose part will be replaced with something else.
position: The position of the text from where the replacement will commence.
length: The total number of characters that need to be replaced.
new text: The text that you will put in between the original text.

The REPLACE function works similarly to that of SUBSTITUTE. To explain this function, let’s use the same table as before and we’ll try replacing the word “Charter” with “License” from cell C5 to D5. Since the first letter of “Charter” starts is the 9th character inside cell C5, we’ll input 9 as the position and 7 as the length as the word constitutes 7 characters.

To get started, click on cell D5.
In this cell, type =REPLACE(C5,9,7,”License”) and then press the Enter key on your keyboard.
You should see the replaced text in cell D5. To copy the formula over to D6, simply drag the square blue dot on D5 downwards to D6.
You’ll see similar results as long as the text string that gets replaced is of the same length and position.

Method 4: Using REGEXREPLACE function

If you wish to position a different text string inside another text string with matching instances, then you can use the REGEXREPLACE function inside Google Sheets. Its syntax looks like this – REGEXREPLACE(text, regular expression, replacement).

text: The text you want to replace a portion of.
regular expression: The matching instances within the text above that you want to replace with something else. This could either be characters, spaces, or numbers.
replacement: The new string of text that’s going to replace a part of the text above.

You can now follow the steps below to use this function inside Google Sheets.

To replace the word “Charter” with “License” in the table below from cell C5 to D5, click on cell D5.
In this cell, type =REGEXREPLACE(C5,”Charter”,”License”) and then press the Enter key on your keyboard.
The replaced text should be visible in cell D5. To copy the formula over to D6, simply drag the square blue dot on D5 downwards to D6.
You’ll see similar results as long as the text string that gets replaced is of the same length and position.

To see other expressions to use with REGEXREPLACE, check out this Google support page.

How to perform a simple search in Google Sheets

If all you want is to locate something inside a spreadsheet, then you don’t need the Find and replace tool. Instead, you can perform a simple search on Google Sheets without replacing any data.

Use the following keyboard commands to open the search bar:
On Windows: CTRL + F
On Mac: CMD + F
In the search bar that appears, type the text, number, or character you want to locate inside Google Sheets.
When Sheets can find something matching your query, it will highlight the words or sections inside the spreadsheet in green.
If there are multiple results for the search, you can switch between them using the up and down arrows inside the search bar.
If you want to expand this search bar into the Find and Replace tool, you can do that by clicking the 3-dots icon from the search bar.
The Find and replace tool will now show up on your screen as before.

How to find and replace text and values on Google Sheets app

Similar to how you use it on the web, the Find and replace tool can also be used on the Google Sheets app on your phone.

Launch the Google Sheets app and open the spreadsheet you want to edit. When the spreadsheet opens, tap on the 3-dots icon at the top right corner.
In the sidebar that appears, select Find and replace.
You will now be able to see text boxes above and below the spreadsheet view. The box and buttons above the spreadsheet will help you find your desired text from the sheet while the ones below will let you type the text you want to replace the portion with.
To get started, type the words or values you want to replace from the search bar at the top and then tap on the search button adjacent to it. When Google Sheets gets you the results, tap on the up and down arrows to switch between the cells that have the searched word or value.
Once you locate a text that you want to be replaced, tap on Replace at the bottom of the screen.
If you wish to replace all instances of the searched word, tap on All.
When you’re done making replacements, tap on the tick mark at the top left corner of the screen.

How to find and replace text in formulas

If you use the Find and replace tool on Google Sheets, you will see results from contents within a cell, and for cells that have formulas in them, Sheets will only include the visible results.

If you wish to search for text within formulas, open the Find the replace tool in your spreadsheet by going to Edit > Find and replace from the toolbar at the top.
To include results from formulas, specify the word or value you’re searching for inside the Find text box and then check the Also search within formulae checkbox. Now, look for cells with your searched word or value by clicking on Find.
When you see the desired results, replace it with characters or values you type inside the Replace with text box and then click on either Replace or Replace all.
Sheets will now replace text that’s included in formulas in your spreadsheet and will notify you about the changes it made.

How to find and replace text in links

Just like with formulas, you can also find and replace text that’s present inside the links you add to a spreadsheet on Google Sheets.

If you wish to search for text within links, open the Find the replace tool in your spreadsheet by going to Edit > Find and replace from the toolbar at the top.
To include results from links, specify the word or value you’re searching for inside the Find text box and then check the Also search within links checkbox at the bottom. Now, look for cells with links that have your searched word or value by clicking on Find.
When you see the desired results, replace it with characters or values you type inside the Replace with text box and then click on either Replace or Replace all.

Sheets will now replace text that’s included in links in your spreadsheet.

How to find and replace in All sheets, Current Sheet or Specific range only

If you’re working on a Google Sheets file that has multiple spreadsheets, you can use the Find and replace tool to either across all spreadsheets or be more specific. For that, you can either search for a text within the current sheet you’re viewing or specify a cell range from any of the sheets to narrow down the search results.

To use Find and replace to search for texts inside a spreadsheet, open the Find and replace tool by going to Edit > Find and replace from the Google Sheets toolbar.
When the tool appears on the screen, click on the Search dropdown where, by default, “All sheets” will be selected.
In the dropdown menu that opens, select from any of the three available options:
All sheets: Searches and replaces all cells across all sheets.

This sheet: Searches for texts and values from a specific sheet that you’re currently viewing.

Specific range: When you select this, you’ll be able to search for text in a cell range of your choice.

Choosing this option will show another text field where you’ll need to enter the cell range in a format like this – ‘Gantt Chart’!B4:E11 where Gantt Chart is the name of the sheet and B4 to E11 is the selected cell range. You can replace this data with your cell range.

If you wish to select a cell range manually, click on the Data range button (marked by a grid icon).

On the next screen, you’ll see the Select a data range prompt above the sheet that will show the cells you select in terms of positioning.

In the sheet, select a sheet and then the desired cell range by selecting one cell and dragging it across your preferred area for search. Once you’re done selecting a cell range, click on OK inside the Select a data range prompt.

How to replace values one by one only

The Find and replace tool gives you a quick way to replace one word from the entire spreadsheet with another one with just the click of a button.

If you want to replace text one at a time, open the Find and replace tool and type a word or value you want to search for within the Find text box. When you enter it, click on the Find button to highlight a matching result on the spreadsheet.
When you find a matching result, Sheets will highlight the cell that shows up first in the results. To replace the words or values one cell at a time, click on Replace at the bottom of the Find and replace box.
To replace more items without replacing them all at once, click on Replace again wherever you wish to substitute the old text or value with a new one. To skip a suggested cell, click on Find until you land on the cell whose content you want to replace.

How to check each suggestion before replacing

If you don’t want to replace a suggestion directly but think you may need to check them before making any corrections, you can do that within the Find and replace tool.

To check a suggestion before replacing, enter the text you want to search for inside the Find text box and what you want to substitute it with inside the Replace with text box. After that, click on Find to highlight a cell that matches the searched word or value.
If you wish to replace the highlighted cell, click on Replace. If you want to skip this suggestion, click on Find again.
This will highlight another cell in the spreadsheet. Depending on whether you want to replace a cell, you can either click on Replace to substitute the contents or click on Find to find the next suggestion on the sheet.

How to go through the suggestions without making changes

If you just want to check a word, character, or value within cells of a spreadsheet, then you don’t need the Find and replace tool to begin with.

You can simply use the CTRL (or CMD) + F keyboard shortcut on your computer to access the Find search bar inside a spreadsheet.
In the bar that appears, enter the text or values you want to search for and when you do, Google Sheets will highlight the results in green color.
You can move between them with up and down arrows.
If you decide to replace the text in some of these cells, you can do that by clicking on the 3-dots icon inside the Find search bar.
This will open the Find and replace tool on your screen.

How to know the last change you made

If you want to take a look at the changes you made previously inside a spreadsheet on Google Sheets, you can do that by following the steps below.

Click on the “Last edit was <some time> ago” or “Last edit was at <some time>” link that appears on the same line as the toolbar at the top.
Alternatively, you can open the spreadsheet’s version history by going to File > Version history > See version history.
You can also access it using the keyboard shortcut below:
On Windows: CTRL + ALT + Shift + H
On Mac: CMD + Option + Shift + H
Inside the version history screen, you’ll be able to see the version timelines on the right sidebar while the changes you made in a particular version will be visible on the left pane.

From here, you can restore the sheet to an earlier version, give a version its name, or duplicate it.

Can you select a range across multiple sheets?

No. Sheets’ Find and Replace tool doesn’t let you choose a custom cell range across multiple sheets to search for texts within them.

If you want to search for text or values across multiple sheets in a file, you will have to stick with the “All sheets” option on the Search dropdown inside the Find and replace tool.

That’s all you need to know about using the Find and replace tool on Google Sheets.

RELATED