In this tutorial, we will explain how to search in Google Sheets. We will cover two simple methods and three methods that could be useful in specific cases. The first simple method, using the simple Ctrl+F for Windows systems, is relatively easy to use. The other one, using Ctrl+H, gives you more options in searching in Google Sheets.
The keyboard shortcut Ctrl+F that we use in the Windows systems also works in Google Sheets! If you are looking for the simplest method for how to search in Google Sheets, this is it!
Here are the steps in using Ctrl+F:
Will this work with numbers in Google Sheets? Yes!
Another way to search in Google Sheets is to use the Find and replace feature. This is useful if you are searching for a term because you want to replace it with another string.
Here are the steps:
1. Access the Find and replace feature. Here are two ways of accessing it:
This will load the Find and replace feature:
2. Type the string you are looking for in the text box besides Find label.
3. Specify the range that will be covered by the search.
By default, Google Sheets will search all the sheets in the opened spreadsheet for the specified string. To change it, click the drop-down box beside the Search label. There are three options: All sheets, This sheet (referring to the active sheet), and Specific range.
There are additional options. We will discuss two of them:
You can use these options to narrow down the search.
4. Once you type a string in the text box beside the Find label (as shown above), the Find button on the bottom of the Find and replace box becomes visible. Click it.
You can also add a string into the "Replace with" textbox if you want to replace the search term with another string.
For this case, the individual cells containing the string are highlighted with blue borders. If there is more than one result, clicking the Find button will highlight the next cell containing the string you are searching for. Once you reach the last result, the message No more results, looping around will appear.
If you want to replace the contents, click the "Replace" or the "Replace all" option. Now Google Sheets will not only find the string, but it will replace it with your specified term.
The SEARCH function is helpful if you want to search within formulas. It returns the position at which a string is first found within text. This means that the function returns a numerical value
=SEARCH(search_for, search_range, start_at)
where
search_for=the string or substring that you are looking for; using text strings or cell references will both work; argument is not case-sensitive
search_cell=the cell where you want the function to search for the string
start_at=an optional argument if you 0nly want the function to start searching at a certain character in the search_cell rather than at the beginning of the cell contents
For example, imaging that we want to search in Google Sheets cells for the term "Lido Users". Using the formula =SEARCH("Lido Users", A2), we can search cell A2 for the the start of this string.
Notice that we did not add the optional third argument because we want to search from the beginning of the cell contents. Also, we could use the cell reference B3 to refer to the substring that we are looking for.
You can see the results of our search in the image below. We get 7, the position of the start of our search string in the larger string.
Check out our tutorial to highlight cells containing matching search query.
This method is useful because you can match entire cell contents.
The REGEXMATCH function is an extremely useful way to test whether cells contain a certain string that returns a TRUE/FALSE value. Check out tutorial about Google Sheets REGEXMATCH.
We hope this article has helped you and given you a better understanding of how to search in Google Sheets. You might also like our articles on how to use Google Sheets data visualization and how to line break in Google Sheets.
To optimize your workflow, we recommend reading our article on how to send an automated email from Google Sheets.
