What is the Find and Replace Google Sheets feature and how do you use it? When spreadsheets are filled with huge data sets, finding and substituting cell entries can be challenging. You can make this process a lot easier by using the Find and Replace Google Sheets feature.
To access this option, go to Sheets and click “Edit” > “Find and Replace.” Follow any on-screen prompts you see to proceed.
This Google Sheets feature offers a lot of convenience, especially when you learn how to navigate its search modes. In this article, I will discuss several search tricks with alternative Google Sheets formulas to replace text and other cell contents in the following sections.
This Article Covers:
Working With the Find And Replace Google Sheets Dialog Box
When I need to find something quickly in a workbook, I use the Find and Replace Google Sheets tool because it is really simple.
Below, I have highlighted the easiest ways to use it:
- On a Google Sheets spreadsheet, click “Edit” > “Find and Replace.”
- Press the keyboard shortcut Ctrl + H (or ⌘ + Shift + H on Mac).
- If you want to find values in your spreadsheet, press the keyboard shortcut Ctrl + F (or ⌘ + F) on Mac. This will open the Google Sheets find box at the top right instead.
1. Replacing Values on Google Sheets
Once the Google Sheets Find and Replace dialog box appears on your screen, you can fill out the text boxes for your query. I have included a sample guide below to make it easier for you to follow how to replace words in Google Sheets:
- In the Find and Replace dialog box, type a specific string you want to look for in the “Find” field.
- Enter the text string you want to replace in the “Replace with” field.
- Adjust your selected range (whether you want to search the current sheet or the entire spreadsheet file) using the “Search” drop-down menu.
- Click the “Find” and “Replace” buttons to substitute the strings individually.
- Alternatively, choose “Replace all” if you want to make multiple edits quickly for your chosen text string.
- Click “Done” to close the dialog box.
2. Find and Replace Case Sensitive Content
Alternatively, I sometimes search and replace Google Sheets content in a narrower range by limiting it to match the exact string. For example, I would enable this feature when dealing with names, email addresses, quiz answers, and other data for capitalization.
In the case of my search query, it would be ignored by Google Sheets by default and allow me to quickly search case-sensitive text by ticking the checkbox for “Match case.”
3. Match Entire Cell Contents
When no other search option is enabled, Google Sheets will automatically highlight every instance of the string that I want to find. This is true even if the cells contain other characters and strings besides my keyword search.
If I only want the cells with the exact set of characters I want to replace, I would tick the option “Match entire cell contents.”
4. Use Regular Expressions
The Google Sheets Find and Replace feature also supports regular expressions (commonly called “regex”) when searching for values. They’re sets of letters, numbers, and special characters that you can use to make a particular search word.
I tend to use the regex feature when I want more accurate searches. From my experience, it’s a great option to use for finding ZIP codes, names with particular letter cases, and money values and currencies.
For example, if I wanted to, I could use the regex “^[a-z].*” to single out all cell entries in lowercase. In this case, I would make sure to tick the option for “Search using regular expressions” in the Find and Replace dialog box. The “Match case” option would automatically turn on, too.
Admittedly, learning about regular expressions can take time, but many resources are available online. One such resource I found particularly helpful is RE2 expressions on GitHub, which uses the same syntax as Google Sheets.
5. Find and Replace Text Within Formulas and Links
The Find and Replace feature in Google Sheets only searches for what’s directly displayed on the cells. If you have written a formula in one cell, the tool will take in the results of that formula instead. You can apply the same principles to links and hyperlinks in your spreadsheet.
There are some ways to show formulas instead of values in Sheets. But you can bypass this step by enabling the “Also search within formulas” option in the Find and Replace feature. To include links in the search range, toggle “Also search within links” as well.
Why Use Find and Replace in Google Sheets
Besides using the Google Sheets Find and Replace option, alternative ways exist to find and replace text and other spreadsheet data. Below, I have highlighted its capabilities and other benefits in the list:
- Multiple search ranges, including entire workbooks, the current worksheet, and even a specific range
- Replacing text in one or all instances of a search string
- Fine-tuned searches using regular expressions
- In-depth searches and replacements by finding strings within formulas and links
- Search case-sensitive content and cell matching
- Usage of wildcards — special characters you can use to find similar values to your search string
Aside from these, the most enticing attribute of the Find and Replace feature is that you don’t need to set up formulas. Everything is mostly done with the user interface and your keyboard (except for relatively advanced concepts about regex).
Find and Replace Formula in Google Sheets
Aside from the Find and Replace feature in Sheets, you can always use custom formulas and functions to search for text in your spreadsheet. Here are two useful formulas you can try:
SUBSTITUTE Function
If you’re mainly dealing with non-numeric characters, the SUBSTITUTE function may be good to use. You can use the following syntax:
SUBSTITUTE(text_to_search, search_for, replace_with, [occurrence_number])
- text_to_search refers to the original string to find characters.
- search_for is the text you want to find.
- replace_with is the one you want to use as a substitute.
- occurrence_number is the specific instance of your search word you want to replace (optional).
You cannot use this for cell entries with numeric characters. But if you insist, a workaround is possible using the VALUE function.
REGEXREPLACE Function
You can also try using the REGEXREPLACE function in this regard. As with the previous function, it doesn’t work on numerical values. It uses the syntax:
REGEXREPLACE(text, regular_expression, replacement)
- text is the original text string you want to check.
- regular_expression is the search regex you want to use (it must be in R2 syntax).
- replacement is the text you want to insert.
Frequently Asked Questions
How Do You Find and Replace Multiple Cells in Google Sheets?
On a Google Sheets spreadsheet, choose “Edit” > “Find and Replace.” Enter your search string and replacement text in their respective fields in the Find and Replace dialog box. Tick the option for “Match entire cell contents.” Then, click “Replace all.”
How Do You Find and Replace With Wildcards in Google Sheets?
You can substitute a wildcard in your search word. Question marks are for single characters, while asterisks are for multi-character strings. For example, you can type “M?n” as your search query, and the results would include text like “Man,” “Men,” “Min,” and others.
Related: Using Wildcards in Google Sheets (4 Examples).
Replacing Google Sheets Content Made Easy
The Find and Replace Google Sheets feature provides the easiest way to substitute cell entries for another value. You must access the tool, enter your search string and replacement text, and click “Replace.” Alternatively, you can use formulas, the SUBSTITUTE and REGEXREPLACE functions if you want to customize your searches.
If you found this formula guide helpful but want to learn more, check out this Google Sheets formula and functions course. It covers basic functions, including IF, COUNTIF, SUMIF, and more! By the end of the course, you’ll be able to use these formulas confidently and increase your spreadsheet efficiency. Happy learning!
Related:
