- [Instructor] All right, if you're using Google Sheets
and let's say that we have these amounts here,
and what we're trying to do is extract the numbers
from the text.
All right, so there's several different ways used to do it.
We're going to go through each one.
(light music)
The formulas are kind of complicated.
I'll have a link to those in the description.
And then we're going to go through the use of an add-in,
which will do the same thing
but it's just going to make it a lot easier.
And you can choose which solution to use.
All right, so first,
let's take a look at a couple of these pieces of data.
And these formulas have different strengths
and weaknesses, all right?
So I arranged these in different ways
so you can see what happens.
And the first two were kind of real life examples
where you might have some text in your cell first,
but really, you just want these numbers.
All right, a phone number here, a ZIP code here.
And then I have an example of something
that starts with a number and you can extract just that,
or we'll go over how to extract the 7 and the 12,
and then some just random strings here.
And then for fun, I just typed in a number,
but you can see because it's shift to the left,
Google Sheets actually doesn't think that that's a number,
so that's really just text.
And then I typed in some actual numbers.
Okay, so these are numerical values here at the end.
I put in a thousand separator on the last one.
So the first solution that we're going to go through
is that we're going to split each block of numbers
into its own cell.
So what I'm saying as a block
is just kind of continuous digits here, 345,
and then the parentheses in the space is a break, right?
And then you go to 678.
So if you want each one of those in a column,
I'm gonna go to my sheet here
where some things are done ahead of time.
And we're going to grab this formula
and we'll talk about it.
So I will copy it,
bring it over to this sheet,
and I'll double-click it.
So what we're doing is we're using the SPLIT formula,
which is going to break apart the value in cell B2,
and it's going to place each little piece in its own cell.
So I've hit Escape real quick.
You'll see, below it it's already working.
It broke out the 345,
then the 678, then the 2345.
All right, let's take another look at it.
And what it is doing is it's saying SPLIT,
which is a predefined function.
If I click on that sheet,
it's telling you how to use it.
But again, we'll all have copies of this
that you can find through the description.
So don't worry about it too much.
But before we split B2,
we're going to change it into all lowercase.
And we change it into lowercase
because this list of characters,
it's looking for all lowercase.
So what we say here is any occurrence of a Q or a W
and you go on and on, and these symbols,
you need to break this out into a separate column.
So that's what SPLIT is doing.
We can drag it all the way down.
And this formula is working pretty well
for what we're doing, right?
So it's splitting out these numbers into separate columns.
Right, even if there's no breaks, no spaces, it works.
So it grabbed the 7.
And then it saw that this was a 0 not an O,
so it put that into column D.
If you come down to where I just typed in the word one,
it doesn't work because it doesn't know
that that's a value, right?
This is shifted to the left,
which is a little hint that Sheets still thinks
that it's a text and not a number.
Another way to check is I'll do the function ISNUMBER.
And it's going to come out FALSE on it.
There we go, it's FALSE, so that's not a number.
So this is one fallacy in this formula.
As you can see, if there is no number in there,
it just pulls the text out, okay?
So if that's a problem, you may not wanna use that.
And then as we come down,
it got fooled by the thousand separator
and it broke this into two different ones.
What you could do is take the comma out of the formula.
So it's right here, let's delete that.
And the formula works.
But if you have a comma just in the middle text,
then it won't do what you wanna do.
Okay, so we had to make some choices here.
I'll put that back to the way it was,
and that's the SPLIT function,
so that's our first example.
Let's go on to the next way to do it.
We'll go to our sheet here where we have a few things done,
and there is a technique to use
that gives you all of the numbers and nothing else.
We're going to copy this,
put it into our cleaner sheet and we'll talk through it.
So this is using the function called REGEXREPLACE.
What this is doing is looking for anything
that's not a digit.
All right, so that would be anything
that's not 0 through 9
and replacing it with a blank,
and so all you're left with is numbers.
The subject of how these regular expressions work
is a little bit outside of the scope of this video.
All right, but this is a regular expression.
There's plenty of reading on it
if you really want to dig into what this notation means.
But for the sake of getting this done with the numbers,
I would just copy and paste this formula.
So that gives you all the numbers just in a string.
We'll drag this all the way down.
And you'll see that this seems to work for most of them.
There are some interesting things to do.
Note that it will give you a 0 if it's all just text
instead of a blank.
Maybe that's okay.
And it doesn't work if it's only numbers.
So it's trying to find something to replace,
but there's nothing to replace,
so instead of just returning the number,
this will give you a #VALUE error.
Now, I'll link on the screen right now
to a video about how to deal with the #VALUE error
and what it means.
The one thing that you can do is just wrap it
in the ISERROR function.
If it's an error, you can just return blank.
But that's what this function does.
So we will delete that, grab the next function,
which is a way in which you can get
just the first occurrence of numbers in a series.
So we'll copy this formula.
We'll come back, we'll talk about it.
What this formula doing is instead of REGEXREPLACE,
it's using REGEXEXTRACT.
So it's doing a regular expression again.
This is looking for all of the digits in a row,
that's what the d is,
and it's pulling them out to this value.
So it's not replacing the non-digits with an empty string,
rather, it's pulling out the values that are digits,
and then it's making sure they're shown as a value
with the VALUE function instead of TEXT.
So we'll pull this all the way down,
and it does get the first occurrence of numbers in a row.
Right, it also gives errors as you get further down.
So this might be a good fit for you, it might not.
But to back up one minute,
so we had talked about how this is using the VALUE function.
That's making these actual values.
So one of the implications of this
is that you can use these results in a function now.
So if I wanted to add 345 and 7 for whatever reason,
the math would work and it would give me a value.
All right, I don't wanna do that autofill right now,
I'm gonna close that out.
I'll delete this 352.
And those are the different solutions.
Between the three of those,
you most likely can find something that you want.
But there is a way to do it that's a little bit easier.
Or at least I think it's easier.
It's using a solution called Power Tools,
and I am affiliated with them.
So if you use the link in the description,
you'll help to support my channel
and you'll also get a discount on the product.
I'm gonna show you one of the things that it does.
I've already highlighted that range.
We go to Add-ons and Power Tools.
Let's just start it up.
It'll pop up on the right-hand side.
I had to switch users here
so you can see what cell my other profile has selected.
We're gonna ignore that because we want to select B2 to B10.
And we're going to be using a text function,
so left-click on Text.
And then we'll go into Extract.
Right, and we're going to use the Extract numbers category.
So there's several different configurations here.
We're going to run a few of them
and talk about what happens.
Let's take off the thousand separator for now,
and we're going to extract all of the occurrences.
And you can put them in separate cells,
so this would be the first thing that we did with SPLIT.
Let's run that.
All right, so 345, 678.
If we look back, it is the same result
that we originally had here.
All right, let's go back.
Let's make sure everything's selected.
We're going to now extract it into one cell.
Left-click on Extract.
There we go, it's pulling all the numbers
and it's putting them all into one cell.
And I'm going to take this out of the full screen.
I'm going to put this over to the right,
and I'm going to get what we did before
and I'll put it on the left.
So this is going to help us compare a little bit.
All right, so I've brought up on the left
what we had just gone through.
So this was using REGEXREPLACE.
This was a technique that brought all the numbers out.
And the downfall with this,
which we had touched on briefly
was that it brought out a 0
when it probably should have been blank.
And then it doesn't work if it's only numbers.
So we look at what Power Tools did here.
It made it blank when there was nothing.
So that one comes as a blank.
You may want a 0, I don't know,
but I think a blank is a little bit better.
And then it does work if there's just numbers.
It just returns to the number.
All right, so let's try to recreate this formula
that we used, which was the REGEXEXTRACT
to get out the first numbers.
So we'll delete these, highlight the range again,
let's look at Power Tools,
and what we can do is uncheck Extract all occurrences.
So now we'll left-click on Extract.
And this just brought out the first numbers that come up.
And let's delete that one more time
'cause now you are seeing why you can tell it
that you have a thousand separator.
So we will select it,
we'll tell it we have a thousand separator of a comma.
So that should help us out with row 10.
(light music)
All right, and that grabbed all the numbers from row 10.
So you get to choose what method works best for you.
Next, we're going to lighten it up,
have a little bit of fun,
make an interactive task list.
And when you check something off,
it crosses out your to-do.
Thanks for coming along to watch this video,
and I'll see you in the next one.