- [Instructor] All right, if you're using Google Sheets
and you find yourself wanting to use
a more advanced find and replace,
we're going to go over a option right now
that gives you some flexibility with the things
that you can look for.
All right, so your traditional way
of looking for something in a spreadsheet,
at least what I would do
is hold down the Control key and hit the F,
as in Find button.
Okay, so you get a little pop up here,
and if you're looking for the word Friday,
this'll bring you right to it.
But if you need more options, what you can do,
you can either go to Edit and Find and Replace,
or just left click on these three dots,
and then you'll get a pop-up menu that's going to give you
the option to replace what you find with different text,
search in different places,
and configure some other items as well.
So these are built into Sheets,
and we're not going to spend too much time going over these
because if you found this video,
chances are, you can't find what you want here.
So we're going to go over
what's called Advanced Find and Replace.
So we will close this,
and we'll do this using the add-in called Power Tools
and there's information on this add-in
down in the description.
And if you use that link, you'll help to support my channel
and you'll get a discount, too,
so check it out there if you do wanna look at it.
We'll go to Power Tools and left click on Start,
and it will bring up a menu on the right-hand side.
And this menu is where everything
is driven from with this plugin.
So if you go to the picture of a magnifying glass and a pen
and left click on that, this is your main menu
that drives the Advanced Find and Replace.
And we'll talk about what options are here
that aren't in the normal Find and Replace.
In this spreadsheet, we'll go over into the table right now
and we'll look at some of these values
that have a note in them.
All right, so what we've done,
we've right-clicked and Insert note.
Now these aren't comments, you can't search comments,
and those are meant to go back and forth
in between users of the spreadsheet.
A note is really just a little piece of added information
that's meant to sit in one cell and add something to it
that doesn't sit inside the cell.
So this cell has a note.
Let's just say this person got a discount.
They got the pen discount.
Down here, this person's from Ohio,
so we charge them full price.
And you have another pen discount here.
Put in a few more as you scroll down.
And if you use Advanced Find and Replace,
instead of looking in values, you can look in notes.
All right, so there's check mark there.
We're going to search the text
and we wanna find all the pen discounts.
We're not going to look in the values.
So if the word pen is in any of these cells,
it's not going to come up
'cause we're going to uncheck that.
As a matter of fact, we'll change one
of these last names to Pen, just to show you that.
And unlike regular Find and Replace,
you can select only certain sheets,
or you can do all sheets or just the current one.
Okay, but this would allow you to do sheet one
and sheet three and skip sheet two, if you wanted to,
but we're just going to do sheet one right now.
Left-click on Find All.
So it's looking around,
and it found all of these pen discounts.
So if you click on one, see that says it's in C47.
It's going to make that the active cell,
and we'll hover over that and see it's pen discount.
It did not pick up the cell that said Ohio full price.
So this is working pretty straightforward, how it does that.
And let's just replace pen with something
to show how that works.
So we'll type P-E-N-N, but we'll change the case.
Replace All.
All right, it shows the new values here.
And if I hover over one,
you can see the new capitalization.
Another important ability that Advanced Find and Replace
gives you is if you left-click on these three dots,
you can export all the entries that you found.
You can export the entire row to which they belong,
or just the entries themselves.
Or if you go back in here
and you just want a chunk of them,
let's just say we want these middles ones,
you can export the selected entries.
So let's try that. Left-click on it.
And it generated a new worksheet
and tells you what sheet it's in,
what cell, where the value was,
so this type was in a note, and the value itself.
All right, so that could be super handy.
It didn't overwrite anything. It made it's new worksheet.
So let's just leave that there and go back to sheet one.
And the next ability that we'll show is
that regular Find and Replace,
you can search using regular expressions, they're called.
But they're really complicated to use.
Find and Replace has something different.
So let's left-click on New Search
and you have the option to match case,
which is in the regular one, and so is entire cell,
but we're going to use By Mask.
Let's turn off the notes and just search in the values.
So this would allow for all
of the traditional search wild cards that you use.
If you haven't used these before,
they're very powerful, and the most popular one,
at least the one I use a lot, is an asterisk.
So what we're saying is we're going to search for the URL
answer.com or answers.com,
and the asterisk there is just saying
any character here is fine.
So we'll left-click on Search All,
and it returned answers.com and answer.com.
You could also just search for all the days of the week
so you could do asterisk day.
Click on Find All, and it's going to return all the values
of the days of the week.
All right, I've added another column
to show one last ability
that Advanced Find and Replace has,
and that is to search for different types of error messages.
So we have a value error message.
We have a div, and we have another value.
All right, so let's look at this value.
What's happening is the word ten here isn't really a number.
So it doesn't know how to use that in its formula.
If you have a very large spreadsheet,
you could have errors throughout.
You need to find each one of them to replace them.
What you can do is you can search for errors
and the text of the errors, so in this case, we have value.
Left-click on Find All,
and it will give you a list of all the values.
And remember that you can export this into another list
and then you can work on fixing them.
So this next video's going to talk about that value error,
why you might get it and how you can fix it.
(mellow music)
Thanks for watching, I'll see you in that next video.