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
- 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.
- Type a word or a series of letters and/or numbers you want to search for in a text box.
- Press the “Enter” key, after which all cells containing the specific word, letters, or numbers are highlighted
- 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
- 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.
- Type a word or a series of letters and/or numbers you want to search for and replace in the text box named “Find”.
- Input a value with which you want to replace the found words if any
- Select the scope of the function, “All sheets”, “This sheet”, or “Selected range” in the “Search” section.
- Select options if needed.
- Click “Find” to use the Find function or “Replace” or “Replace” to use the Find and Replace function.
- 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.
- Range Selection: No match or too many matches? Review if you select the search range appropriately.
- 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.
- 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.
- Cell protection: You can’t change the values or formulas in cells if they are protected. You need to unlock the cells first.
- 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.
- 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.
.png){kind=small}
