How to Find and Replace Words in Google Sheets

In this article, you will learn how to use the Find and Replace functions in Google Sheets. The Find function is beneficial when you simply need to find cells containing a specific word. The Find and Replace function is helpful when you want to see and replace a particular word or letter with a new word or different letter in multiple cells.

‍

How to use the Find function in Google Sheets

1. Press “Ctrl”+”F” (for Windows) or “⌘”+“F” (for Mac), which brings up a small pop-up window at the top right corner of your window.
2. Type a word or a series of letters and/or numbers you want to search for in a text box.
3. Press the “Enter” key, after which all cells containing the specific word, letters, or numbers are highlighted
4. Press the “Enter” key repeatedly to move to the next cell, or click the icons next to the text box to go back to the previous cell (“∧”) or move to the next cell (“∨”).

‍

How to use the Find and Replace function in Google Sheets

1. Press “Ctrl”+”H” (for Windows) or “⌘”+“Shift”+“H” (for Mac) or navigate to the “Edit” tab → “Find and replace”. Then, a pop-up window shows up in the middle of your screen.
2. Type a word or a series of letters and/or numbers you want to search for and replace in the text box named “Find”.
3. Input a value with which you want to replace the found words if any
4. Select the scope of the function, “All sheets”, “This sheet”, or “Selected range” in the “Search” section.
5. Select options if needed.
6. Click “Find” to use the Find function or “Replace” or “Replace” to use the Find and Replace function.
7. Click “Done” to close the window.

‍

Search section

All sheets: With this option, the function search entire files for a specific value.

This sheet: The function seeks a specific value in the worksheet you are working on.

Selected range: The function's scope is limited to a chosen field on the current working sheet.

‍

Search method

Match case: If you check this box, a search becomes case-sensitive.

Match entire cell content: The search returns only exact matches.

Search using regular expression*: The function tries to find words and values that meet a regular expression.

Also search with formulas: The search includes formulas in its result.

Also search with links: The search includes links in its result.

‍

*Note: “Regular expression” is a sequence of symbols and characters showing a string or pattern to be searched for within a longer piece of text or value.

‍

Find, Replace or Replace all

Find: Choose this button if you want to conduct a value search.

Replace: You can determine if you replace a word while going through all found values.

Replace all: Click this option if you want to replace all searched values with alternative values. If you are not confident, we recommend you choose “Replace” and decide if you replace by checking values one by one.

‍

How do I automatically replace text in Google Sheets?

There is no function that automatically replaces existing texts or values with their substitutes, but you may consider using the REPLACE function that allows you to replace a part of values in cells with different values.

‍

Why is find and replace not working in sheets?

Check the following major points if the Find and Replace don’t work in your Google Sheets.

1. Range Selection: No match or too many matches? Review if you select the search range appropriately.
2. Case-sensitive/Exact Match: If you have too search results, consider checking boxes for “Match Case” or “Match entire cell content” to reduce the number of words found.
3. Formulas and links: you need to check the boxes next to “Also search with formulas” or “Also search with links” to find keywords in formulas or links.
4. Cell protection: You can’t change the values or formulas in cells if they are protected. You need to unlock the cells first.
5. Filter view/Grouping: The function can find words in hidden or invisible cells because of the filter view or collapsed rows and columns, but it doesn’t show them. You need to consider stopping the filter view or expanding the collapsed rows and columns.‍
6. White space(s): Even if values in cells look right, they might contain extra space(s) at the beginning, end, or middle of texts. Make sure extra spaces are removed from values, and you use space correctly when you type in a search word.

‍

What are the other advanced tips in Google Sheets?

If you don’t get the solution to your question in this article or you are interested in learning the other advanced tips in Google Sheets, you may find the answers in the following articles.

‍

Check the following articles to use Google Sheets more efficiently.

Named Range in Google Sheets: Explained

How to Enable Excel Shortcuts in Google Sheets

Relative Reference and Absolute Reference in Google Sheets: Explained

‍

Read the articles below to organize your data in Google Sheets.

How to Remove Duplicates in Google Sheets

Pivot Table in Google Sheets: Explained

‍

Go to the following pots to learn how to create a pull-down menu or limit data input in a cell.

Drop Down List in Google Sheets: Explained

Dependent Drop-down List in Google Sheets: Explained

How to Use Data Validation in Google Sheets

‍

Do you want to learn how to use Google Data Studio? Navigate to the article below.

What is Google Data Studio?

‍

Analyze your live financial data in a snap

Are you learning this tip to visualize financial data, build a financial model, or conduct financial analysis? In that case, LiveFlow may help you automate manual workflows and update numbers in real-time. You can access various financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.