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.
Ctrl+F
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:
- Press the keyboard shortcut Ctrl+F. A small box for entering search text will appear on the upper-right corner of Google Sheets.
- Type the string you are looking for inside the text box. Google Sheets automatically highlights the cells containing the string you entered.
- If there is more than one cell containing the string you are looking for, press Enter until the cell containing what you need is highlighted.
Will this work with numbers in Google Sheets? Yes!
Ctrl+H: Find and Replace
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:
- Use the keyboard shortcut Ctrl+H.
- Go to Edit, then click Find and replace from the drop down menu.
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:
- By default, the case of the characters of the string is ignored. Ticking the Match case option means the case of the characters must also match.
- By default, Google Sheets will highlight the cell that contains the string, whether it is the only string inside the cell or not. Ticking the Match entire cell contents box will make Google Sheets highlight the cells that contain only the specified string.
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.
Google Sheets SEARCH Function
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
Syntax+
=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
Example
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.
Use Conditional Formatting to highlight matching search queries
Check out our tutorial to highlight cells containing matching search query.
This method is useful because you can match entire cell contents.
Search With Regular Expressions
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.
Recommended articles
More resources
