Multiple Cell Data and Formula Reference With Find and Replace – Google Sheets

One of my biggest regrets when I first got started with Google Sheets and spreadsheets in general, was not taking full advantage of Find and Replace.

Find and Replace is the penicillin of the Spreadsheet world. It can rapidly cure all sorts or issue with a simple set of commands. It is seriously amazing stuff.

Yeah! Yeah! We all know about Find and Replace.

I know! I thought so too, but then I started to really use and identify how I could use it to quickly:

• Change template sheets.
• Fix bulk errors in formulas.
• Change parts of cells.
• Replace values in the whole spreadsheet, one sheet or a selected range.

Before we get started, you need to know the short cut for the Find and Replace tool. This will come in handy in all sorts of programs and apps.

• PC – Ctrl + H
• Mac – CMD (⌘) + H

I’m going to go ahead and continue the examples using PC, because, you know, Mac.

The Example

My wife is gearing up for a Harry Potter party tomorrow (we’re so mature). So to keep in the theme lets imagine we have a set of grades for students in each of the 4 classes:

1.  Defense Against the Dark Arts
2. Transfiguration
3. Flying
4. Herbology

I was away sick (Sick days? This is a fantasy world!) the day the teachers had to put in grades, on the spreadsheet. Unfortunately, I came back to a heap of mistakes.

Here is a list of corrections I need to make. We will use Find and Replace to quickly fix each issue.

1. Change the year to 2018-2019. – All Sheets
2. Find the Dashes in the Defense Against the Dark Arts course grades and get rid of them. – Selected Range, Character Remove
3. Find the lowercase “week” and replace it with “Week” – All Sheets, Case Sensitive
4. Lower the overall passing grade to 60 out of 100 – All Sheets, Include Formulae
5. Find the lone “Week” with no number and replace it with “Week 3” – All Sheets, Regular Expressions
6. Find and remove the spaces from the student numbers in in the Herbology Class – Selected Range, Remove space

1. Change the Year to 2018-2019

We’ll start off with my mistake first. I sent them a grading template with the wrong year. What can I say, I was fantastically sick (see what I did there?)!

We want to change the old date from 2017-2018 to 2018-2019. To do this we will do the following:

1.  (optional) Generally, I like to copy the value I want to change to I don’t make an error. So I am going to go ahead and click the A1 cell in the DADA sheet and then in the formula bar select 2017-2018. Then hit copy (Ctrl + C)
2. Ctrl + H – Boom!!! Find and Replace (Why am I so excited by this?)
3. In the Find field paste the year block, 2017-2018 – Ctrl + V.
4. Next to Replace with, add the new year 2018-2019.
5. Make sure Search is marked, All Sheets and then go ahead and click Replace all.
6. You get a pop-up window warning you that you are about to make changes to ALL sheets in the spreadsheet. Go ahead and click okay.
7. You will see the number of changes that were made towards the bottom of the Find and Replace Window.

2. Find the Dashes in the Defense Against the Dark Arts course grades and get rid of them.

Expelliarmus!!!

Boy! I’m really getting into this.

Our Defense Against the Dark Arts teacher is a bit of a drongo. For some reason the teacher put dashes in the weekly grades. The Weirdo.

We can’t just do select All Sheets again and find and replace the dash (-). That would remove the dash in the year too. Likewise, we can’t just select the sheet and do the same thing.

This time around we will have to:

1. Select the grade range (B3:E18)
2. Ctrl + H – Open find and replace.
3. Add a dash (-) to the Find field.
4. Leave Replace with empty.
5. Ensure Search is set to Specific Range with the correct range indicated.
6. Hit replace all.

3. Find the lowercase “week” and replace it with “Week”

This one is a bit of a combo move.

You’ll have probably been able to see that we put a lowercase “week” in “week 2” for each of the classes and in the Herbology class the number is also missing.

Now if I used the basic Find and Replace to change the second week of Herbology to Week 2 you’d find that all other Weeks would look like this.

Oh no! It’s gone and added a “2” to everything!

How to we work around this?

Fortunately, the mistake with the missing week number is also written in lower case, so to fix this all we have to do is select Match Case change the Herbology class first and then go ahead and change all the other weeks to have an uppercase “W”.

1. Go to the Herb sheet.
2. Ctrl + H. Find and Replace.
3. Find “week”.
4. Replace with “Week 2″.
5. Search “This sheet”.
6. Click Match case.
7. Click Replace all
8. Change Replace with to “Week”
9. Change the Search to “All sheets”
10. Keep Match case selected.
11. Click Replace all. This replaces the remainder of the lower case “week”.

4. Lower the overall passing grade to 60 out of 100

This one I have found immensely useful when creating a large complex template for varying types of grade levels in my real job.

So in this scenario, we have provided the passing grade as 62 as you can see in our “if” formula:

Dumbledore, a man of infinite generosity, has ordered me to lower the passing grade for all student for all courses to “60”.

Fortunately, there is a checkbox in Find and Replace that allows us to also search in formulae.

Here’s how we do it.

1. There might be a lot of 60’s in the grades and we want to be able to quickly edit All Sheets without editing the grades themselves. So let’s also add >=62 to our Find.
2. Replace with: >=60.
3. Search “All Sheets”.
4. Check the Also search within formulae box.
5. Replace All.

5. Find the lone “Week” with no number and replace it with “Week 3”

This one is quite tricky. You can see in some of the classes that Week 3 has a missing “3”.

Unfortunately, if we just Find “Week” and replace with “Week 3” we will get something crazy like:

Now, we can’t do the trick we did in part 3 with the lowercase cause all the cases match. So how do we deal with this?

With regular expressions. Regular expressions are a kind of shortcode for helping us search for particular strings of characters or patterns. These are extremely powerful tools for traversing data. You can find out more about how Google Suite handles regular expressions here.

To decide on what type of regular expression we are going to use we need to consider what is unusual about what we are searching for. For us, “Week” is the only value we want. So if we look for a cell that ONLY ends in “Week”, then we should be able to change the correct cell.

For our purpose, we are going to simply use the Regular Expression “$”. This is used at the end of an expression to say that whatever came before it is referenced from the end of the string of characters.

In our case, “Week$”. “Week” will always be at the end as opposed to “Week 1”, which ends in “1”.

Okay, let’s go ahead and add the regular expression to our find and replace:

1. Ctrl + H: Find and Replace
2. Find: “Week$”.
3. Replace with: “Week 3”.
4. Select Search using regular expressions.
5. Replace all.

6. Find and remove the spaces from the student numbers in the Herbology Class

Evanesco!!!

It’s Harry Potter for vanishing something. In my case something that’s…not…there? Okay. I didn’t think that one through.

Should have gone for the one about taking the wrong herb and being not able to see straight.

 

Firs we don’t want to get rid of all spaces in our cells so we are just going to select the Student ID range.

Let’s go through the steps:

1. Select the student ID Range in Sheet Herbs.
2. Ctrl + H : Find and Replace.
3. In Find, put a single space ” “. Yep, just a space.
4. In Replace with, leave it blank. This will indicate no space.
5. Make sure your Search is set to “Specific Range” and your range is correct.
6. Click Replace all.

That’s it. That’s all you have to do.

I remember the first time I came across this problem it was with thousands of phone numbers a client gathered from people. Some had spaced them all over the place and all the client needed was for the spaces to be removed.

It was just a bit of trial and error before I stumbled on this gem.

Conclusion

Now our Hogwarts grades list is looking spiffy.

Hopefully, you found something new from this list of Find and Replace tips.

Have you got any tips to share? Got and cool regular expressions that you frequently use?

Did you enjoy the tutorial? Want to upskill and get a solid step-by-step course to become a pro at Google Sheets? Check out my course, Google Sheets: Learn the Essentials with Three Detailed Projects. Sign up today.