Find and Replace

You will almost certainly need to search for a piece of information within a Google Spreadsheet, and it should come as no surprise that it’s a pretty straightforward affair since it’s Google we’re talking about; this is what they do!

If you’re working on a Mac, then you can bring up a simple search window by clicking Cmd+F or Ctrl+F if you’re on a PC.

On the face of it, It’s a basic, uninspiring little window where you can simply enter some search text, hit enter and let Google Sheets find what you’re looking for, and it does the job just fine; but there is more under the hood that needs exploring, so let’s take a look.

Basic Search

The basic search allows you to locate the text within a cell. The little find window will tell you how many times it found that particular text string (1), and the cell will be highlighted (2) with the searched text.

If you have more than one occurrence of the text, you can use the up and down arrow buttons to toggle through them, and Google will highlight each cell as you go.

OK, moving along, it’s also a breeze to search across multiple sheets and within formulas, but to do this, you need to utilise the ‘Find and replace’ window.

Find and replace

You can bring the find and replace window up by clicking the three little dots in the find window adjacent to the up and down arrows or by using the shortcut keys Cmd+Shift+H on a Mac or Ctrl+Shift+H on a PC, and if that’s not enough, you can also locate this in the edit menu.

They all bring up the same window, so let’s dive in and see what this can do.

First up, you can simply find text as before by typing the text string into the ‘Find’ box (1) and clicking on the ‘Find’ button (2). If you repeatedly click the ‘Find’ button, Google Sheets will show you every instance of the text on that sheet and any other sheet. It will loop through them all one by one.

This field is case insensitive, so typing ‘carol’ or ‘Carol’, for example, will locate this text no matter how it is within the sheet.

Using the find and replace is just as easy; just type in the text you want to find (1) and the text you want to replace it with (2) and choose whether you want to replace this text on all sheets, just the sheet you’re on or within a specifically selected range of cells (3) and hit the ‘Replace all’ button (4).

I chose the option of ‘This sheet’; you can see it found eight instances of this search text and changed them for me. This is a real-time-saver, so it’s worth using without a doubt.

If you choose the option to replace on all sheets, you will get a warning message pop up just to check before you proceed.

If you choose to only search with a specific range, you need to specify the range to look in; more on this shortly.

Match entire cell contents

Let’s look at matching the entire cell contents because this will inevitably be necessary in a case like this below.

You’ll notice that I’m looking to replace ‘Complete’ with ‘Completed’, but if we do not click the option to ‘Match entire cell contents’, any other instance of the text ‘Complete’ will also be affected.

The image below shows that the text we wanted to change (1) worked just fine, but look at other instances (2 and 3); these changed to ‘Completedd’ because Google found ‘Complete’, and it changed that to ‘Completed’, but there was also a letter ‘d’ left over.

Ticking this ‘Match entire cell contents’ resolves that problem as it tells Google Sheets to only search for the text ‘Complete’; nothing else will do. This only replaced one instance in cell C2, and everything else remained the same.

Search within formula

We can extend this a little further by searching for text within a formula. This example below counts the occurrence of the word ‘Complete’ in the table, but since we just changed any instance of the word ‘Complete’ to ‘Completed’, it shows the count as zero when we can actually see this should be three.

So let’s try that search again, but this time, we’re going to search in a specific range (1) as the search type.

So first, you need to choose the range; I’ve selected the entire F column in the example below. Then check the box to ‘Also search within formula’ and hit ‘Replace all’ to see the magic.

You can see the formula text has been updated, and now it works as expected; it has counted three instances of the word Completed in the table, perfect.

Match Case

Previously, search text was case insensitive; in other words, it doesn’t matter what you type, it will find it regardless of upper or lower case, but when you check the box to ‘Match case’, then the case matters.

You can see in the example below PENDING is written in UPPERCASE (1); therefore, in the Find box (2), you will need to explicitly type what you want to find when ‘Match case’ is ticked (4). I’m going to replace this with the text ‘Pending’ (3), so all we need to do is click the ‘Replace all’ button to finish the job.

That’s all there is to it from a fundamental level; this will handle most search and find and replace scenarios. Of course, it is possible to go one step further by using a regular expression to search for a pattern, but this is beyond the scope of this lesson.

We will dive into Regular Expression in a separate course. This is a powerful tool to have in your arsenal and can come in extremely handy, not just for searching for text but for manipulating it in other ways you can only dream of.