Searching in Google Sheets

If you deal with a large amount of data, searching for particular values becomes very useful.

To open the search box in Google Sheets, use this keyboard shortcut:

Ctrl + F

The same shortcut is used in almost any other application.

Press Ctrl + F and start typing. In the upper-right corner, a search box appeared. On the right side of the bar, there is a number of matching text.

Each time you type, Excel will automatically highlight cells with the matching text.

Find and Replace

If you want to replace one value with another, you can click three dots on the right side of the search box.

You can also activate the Find and Replace window under Edit >> Find and Replace. You can also access it using the Ctrl + H shortcut.

Let’s change a few options. Normally, the search will be done in all sheets, but in this case, we are going to limit this operation to only the ones that are currently selected.

The Match case option will replace only the “ca” string, and not “CA”, or “cA”.

If you click Replace all, the “ca” string will be replaced with “CA”.

Search for text in a range

Besides searching the active sheet or all sheets, there is the third option, called Specific range. You can select a range of cells or a single cell to which the replacement operation should happen.

We are going to replace “CA” back to “ca”, but only for selected cells, namely, the range from C7 to F11.

You can do it by typing the range into the textbox or using the Select data range button.

Let’s try the latter option. After you click the button, the Select a data range window will appear.

Now, you can drag the range of cells.

After this operation, the new range will be automatically inserted in the Select a data range window.

Click OK to go back to the Find and Replace window.

Click Replace all to replace values in the range.

The values in cells C7 and D8 are replaced, but the value in cell B7 stays the same because it wasn’t inside the selected range.

Search and replace functions

There are two functions in Google Sheets that allow you to look for a string: SEARCH and FIND.

The only difference between them is that the SEARCH function is case insensitive, and the FIND takes a case into consideration.

Let’s take a look at the first example:

Both formulas are going to return the same result: 10.

The first appearance of the word “ant” is at the 10^th position.

Now, let’s create a formula that we can see differences between them.

The first example returned the number 10 again, while the second one 22. Because the FIND function is case-sensitive, so the first occurrence of the word “and” is at position 22.