In this video, we're going to look at a few different ways of extracting data from within your cells in
in your Excel spreadsheet. So, the first formulas we're going to look at are your LEFT, RIGHT and MID formulas.
So...
the LEFT formula, as the name implies, you're going to be extracting data from the left side of the cell. So, for example, we type =LEFT and
let's say we need just those first 2 digits before the dash, where it says "Text" here, we want to just choose the first cell,
then comma and how many characters do we need? Well, we need those first two before that...that dash, so
we'll say 2 and you see that only the first 2 numbers were extracted.
Now, the RIGHT formula works the same way, except you're actually counting from right to left. So, we'll type =RIGHT
and we'll choose our cell again. And we want the 3 numbers coming from the right side of the cell.
And there we have our 237 for that one. Let's copy the formula down.
Then for MID, the MID formula actually lets you extract data from the middle or anywhere inside the cell. So, let's say we want
to grab, in this example, we want to take those 2 numbers that are in the middle of each one. So like 22, 23, 14 and so on.
So we type =MID and we choose our cell again,
and then the starting number
is where we want Excel to begin counting characters. So in this case it'll be 4 because, as you see, 22 is actually the fourth character;
1, 0 and dash are actually the first three. So, we want to start counting in that...that
fourth character. And then after that fourth character we only need two numbers - so the 22 . So we type 2
and you see that 22 was brought back. So we just got those two digits.
The next thing we're going to do, and for this one it's better if you have your numbers already highlighted, will be your Text to Columns.
And that is actually located under the Data tab.
If you look up, I'm on the Data tab, and you choose "Text to Columns."
Now what comes up, um,
will be options on how to separate this data into its own columns. So, the "Delimited" function, which we're going to look at first, lets you
choose based on how it's formatted - so whether it's commas, tabs, spaces, etc.
So, we've chosen "Delimited". We click Next and, we don't need "Tab" because we're not using Tabs here, but we do need the "Other"
and, what ours is going divided by is going to be those dashes. So we want to put the dashes in. And when I did
that, you see down here in the "Data preview" how all of a sudden there was these...these
breaks
where the dashes used to be. And that's exactly what we want. So we click "Next" and our Preview is there again,
yes, we want to format it in "General," we click "Finish" and you see how now what was a,
um, a longer Product # is now split up into individual columns.
Now, for the third example, still looking at the Text to Columns - now we're going to look at that "Fixed width" option. Now in this one, we've
highlighted our data again, and in this one, we need to tell Excel where we want our breaks.
So, in this case we don't need the dashes. So, you see here, under Preview, where I'm clicking,
and I'm entering exactly where I want the breaks done. Now, if you want to move one, all you do is just drag it, like I'm doing here, but I
want to leave it right where it is. So, this is how we want our breaks to happen. Click "Next" and it looks a little hairy, but that's what we want.
And we click "Finish" and you see how everything is in its own column. Now, this one preserved our,
um, our dashes, but we can always just highlight those columns to get rid of those.
And those are 3 different ways of extracting data! So, if you enjoyed this video, please make sure to subscribe to this channel - we'll
have some new videos coming up, including one soon on a little more advanced way to extract data.
So, please remember to subscribe and thanks for viewing. Thanks!