How-To

How to Fix Google Sheets Find and Replace Not Working

By Adam Davidson

Published April 7, 2024

Unable to use Find and Replace in Google Sheets? Here’s what to try if you’re having issues with Google Sheets Find and Replace not working.

If you’ve ever used a spreadsheet, then at some point you’ve almost certainly tried to see how far you have to go before you reach the end of the cells. To save you some time, Google Sheets has a maximum of 10 million cells, with a maximum of 18,278 columns (up to row ZZZ). That’s a lot of cells. With so many cells, replacing one bit of text with another could take a huge amount of time. That’s where the Find and Replace tool can help. This tool can search for any cells that contain specific text and replace it with any other text you want. Sometimes, however, this tool may not work as you expect. Here’s what to try if you’re having trouble with Google Sheets Find and Replace not working.

Ensure You Haven’t Selected Match Case

Several search options within Find and Replace can affect the results. If you select any of these options unintentionally, you may find that Find and Replace does not return the results that you expect it to.

One such option is Match Case, which will only find text within your spreadsheet if it matches the same case as the text you enter in the Find field. For example, if you search for Eggs, it will not find or replace the word eggs, since this word does match the capitalization of the letter E. You can turn off the Match Case setting in the Find and Replace dialog.

How to Disable Match Case in Find and Replace on Google Sheets

1. Select the cells that you want to search or make no selection to search everywhere.
2. Click Edit.
   
3. Select Find and Replace.
   
4. Alternatively, use the keyboard shortcut Ctrl+Shift+H on Windows or Cmd+Shift+H on Mac.
5. Enter your search term in the Find field.
   
6. Enter the text you want to replace it with in the Replace With field.
   
7. Ensure that Match Case is unchecked.
   
8. Click Find to find the next instance of the text you are searching for.
   
9. Click Replace to replace this text.
   
10. Alternatively, click Replace All to replace all instances of the search term found in the document.
    

Ensure Match Entire Cell Contents Isn’t Selected

Another reason why Find and Replace may not be working as expected is because Match Entire Cell Contents is selected. This option will only find or replace text if the cell’s entire contents match the search term. For example, if you’re searching for cats but the cell contains cats and dogs, the word cats within the cell won’t be replaced, since the entire contents of the cell contains more text than just the word cats. You can turn off this setting in the Find and Replace dialog in Google Sheets.

How to Disable Match Entire Cell Contents

1. Select the cells that you want to search.
2. Click Edit.
   
3. Select Find and Replace.
   
4. Enter the text you want to search for in the Find field.
   
5. Enter the replacement text in the Replace With field.
   
6. Ensure that Match Entire Cell Contents is not checked.
   
7. Find and Replace should now find all instances of the search text, regardless of what other text is in the cells.

Allow Searching Within Formulas

By default, Google Sheets won’t search for text that is part of a formula, as cells will display the results of a formula rather than showing the formula itself. If you want to find text that is included in your formulas, then you’ll need to ensure that you turn on the Also Search Within Formulas option. This option will search both the text within cells and the text within the formulas.

How Do I Search Within a Formula in Google Sheets?

1. Select the cells that you want to search or make no selection to search everywhere.
2. Click Edit and select Find and Replace.
   
3. Enter the search text in the Find field.
   
4. Enter the replacement text in the Replace With field.
   
5. Check Also Search Within Formulas.
   
6. Any cells containing formulas should now display the formula rather than the result.
7. Click Find to locate the search text within all cells, including those containing formulas.
   

Allow Searching Within Links

If the text you’re searching for is within a web link, Google Sheets will ignore it by default. However, you can turn on Also Search Within Links to extend the search to include any text within your web links.

1. Select the cells that you want to search or search everywhere by making no selection.
2. Click Edit.
   
3. Click Find and Replace.
   
4. Enter your search text in the Find field.
   
5. Enter your replacement text in the Replace With field.
   
6. Ensure that Also Search Within Links is checked.
   
7. You will now be able to find your search text in web links as well as other text.

Ensure That Regular Expressions are Accurate

Find and Replace in Google Sheets allows you to use regular expressions for more powerful searching options. However, if your regular expressions are not correct, you may not get the results you are expecting. You can use a list of regular expression syntax to help you formulate your regular expressions correctly.

1. Select the cells that you want to search.
2. Click the Edit menu.
   
3. Select Find and Replace.
   
4. Enter your regular expressions in the Find field.
   
5. Enter your replacement text in the Replace With field.
   
   
6. Check Search Using Regular Expressions.
   
7. You can now search using the regular expressions that you entered.

Check That The Correct Cells are Selected

A common reason why Find and Replace doesn’t work as you expect is because it’s only searching within a specific area. If only one cell in your spreadsheet is selected, then Find and Replace will search all cells by default.

However, if you’ve selected multiple cells before you launch the Find and Replace tool, then by default, it will only search within the cells that you’ve selected. It’s possible to change this selection from within the Find and Replace dialog box.

1. Select the cells you want to search.
2. Click the Edit menu.
   
3. Select Find and Replace.
   
4. To change the cells to search within, click the current selection next to Specific Range.
   
5. Enter a new range to search within or drag out your selection in your spreadsheet and click OK.
   
6. Find and Replace will now search within the selected cells.

Select the Correct Sheets to Search

If you don’t select a range before opening the Find and Replace tool, the default is to search within all sheets in your document. However, it’s possible to limit the search to the current sheet instead.

1. Click Edit.
   
2. Click Find and Replace.
   
3. Click the Search drop-down.
   
4. Select This Sheet.
   
5. Your search will now be limited to cells within the current sheet.

Remove Additional Spaces

A simple issue that can cause Find and Replace not to work as you would expect is the inclusion of additional spaces in your search term. If you type a space after your search term, the tool will only find and replace cells that include a space after the text that you have entered into the cell. Single instances of the search term won’t be found. If Find and Replace doesn’t seem to be locating text you know is in your cells, ensure that you haven’t typed an extra space by mistake. You should also check the spelling of the terms that you’re searching for and the cells that you’re searching.

Remove Cell Formatting

Some cell formatting can confuse the Find and Replace tool and make it miss text that it should find. You can remove the formatting from your data, which may get things working properly again.

1. Select all the cells that you want to remove the formatting from.
2. Click the Format menu.
   
3. Select Clear Formatting.
   
4. The formatting will be removed from your selected cells.
5. Try Find and Replace again.

Remove Hidden Characters with the CLEAN Function

Some of your cells may contain hidden characters, especially if data has been copied from other sources, such as a website. These hidden characters can stop Find and Replace from working correctly. You can use the CLEAN function in Google Sheets to remove these hidden characters.

1. Select an empty cell and type =CLEAN(
   
2. Select the cell you want to remove hidden characters from.
   
3. Type ) and press Enter.
   
4. The new cell will contain the text of the original cell with any non-printing characters removed.
5. Repeat for any other cells you want to clean.
6. Delete the original cells and try Find and Replace again.

Fixing Find and Replace in Google Sheets

If you find you’re having problems with Google Sheets Find and Replace not working, hopefully, one of the fixes above has solved your issues. If you find any other useful methods, please let us know in the comments.

Find and Replace is a useful tool but it may require some tweaking to get it to do exactly what you want. If you want to take your search skills to the next level, then learning how to use regular expressions can make the tool much more powerful.