3. Find and Replace

So far we have explored commands that make our lives easier when we’re analyzing data. But what about fixing a “bug” in a spreadsheet?

As an example, the maximum score for the midterm was 26 (look at the “Total Max” in row 25 to check this). Suppose you realized that a few students have a score of 30 entered for their midterm (one of the graders was confused) -- but you have no idea exactly who has those extra points. We’d want to fix their scores to give them the real maximum score – so we’d want to find all occurrences of the number 30 in the range M2:M91 and replace each 30 with the number 26.

Find & Replace is a function that takes two parameters, the string that to look for (e.g. find) and the string that should replace it. Then, there are optional features that provide more strict rulings, such as

· matching the same exact case (so cRaZy != crazy),

· match entire cell contents (if a cell has the word cat in it, at would not return that cell as a possible match)

· Search all sheets (if checked then it checks all sheets in the workbook, otherwise it only searches the current sheet)

· Search using regular expression (Something programmers utilize everyday, this can be ignored for most people).

Note: Even though we describe find and replace for “strings” (text) it also works for numbers (like 30). But the extent of interesting stuff you can do with it is more interesting for strings – so we wanted to tell you about it.

In addition, the find function allows a person to just find the result, replace only one result, or replace all results.

To open up Find & Replace, go to Edit and click on Find & Replace. There is also a keyboard shortcut that is different between macs and pcs.

The following window should pop up, and we can then input our parameters.

We need to find the students who received 30 on their midterm. So let’s type 30 into the Find parameter, check the Match entire cell contents button. Then lets click on Find.

<click on me to make me bigger>

Oh look, a person named Barack has gotten a 30. But what if they’re not the only one? You can press Find again and it’ll find the next one.

<click on me to make me bigger>

If there are no more results, Spreadsheets will give an error message saying that no more results are found and it’s going back to the first result.

We figured out that those students should automatically get 26 on their midterm. So let’s fill in the Replace With parameter with 26, and then press Replace All.

Once we clicked on Replace All, it will tell you that

It has finished replacing all instances.

PRACTICAL NOTE: Many times when you are fixing an “error” like this, it can be good to step through the find-only process first to get a feel for the things you will be replacing. That’s why we walked you through just find first, then used Replace all.

<<Back Next>>