Use the FIND, MID, LEFT, and RIGHT functions

- [Instructor] Excel has a number of functions that fall under the heading, text. If you go to the Formulas tab on the Ribbon, you'll see Text and lots of options here. A few more common ones are left and right, where we can pull data out of the left side of a cell, right side of a cell. For example, in column A here, we've got some item numbers. Maybe these are set up on a positional basis. Maybe it's the three leftmost characters that refer to the actual location where the item was made, maybe that's something to do with the size, or the color or the price, something like that.

Let's pull out data from the left side of these. Now, in some of the examples that we're going to be seeing, a faster and perhaps better approach could be the use of the Flash Fill feature. But let's show how we can first use the left function. We're about to pull out data from the left side of this cell. Here's the location and we want to pull off three characters. Type it this way, press Ctrl + Enter, we see what's happening. Double-click to copy down the column. Simple and straightforward. If we simply wanted to pull off data from the right side, automatically you know that function if you know this one.

Maybe we're trying to extract two characters in this case. Two characters from the right side. Double-click, copy down the column. But pulling out data from the middle involves a different function, a little more involved. It's called mid, and again, think of the word middle, although not in all cases literally the middle. We're about to extract some data from A2, and let's say we're starting in the second position here. We need the second, third, and fourth characters. So starting at the second position, comma, if we're trying to pull out three characters, based on what we're seeing in cell A2, we're about to see k0y because we're starting in the second position reading from left to right, three characters.

You see what's happening. Copy down the column. It's working in all cases. So left, and right, and mid are frequently used. Now when we're using these two, keep in mind a great shortcut, not so well-known, but should be well-known because it's such a time-saver. Any time you're using functions like this, they creates results that you want to keep, what you really want to do, in some cases, and let's say this is one of them. We want to throw away the data in column A and just keep this. So what do we do? Without going through any commands, simply take this data and use the right mouse button to drag it on top of the old data.

When we release the right mouse button, here's a menu. Copy Here as Values Only, now the wording could be a bit more accurate, but what it really means is take the results of those functions we see in column B and paste them here. There's no formulas left here. Over here we still have the other formulas working off this data, but we just get rid of that eventually. So we have no formulas here, we've got the results placed here. Now I'm going to undo this with Ctrl + Z and point out that there could be times when you say I want to keep that data over in column A, but I also want to keep this but not as formulas.

We can copy this to itself, point to any edge with the right mouse button, and temporarily, and in one smooth motion, drag it elsewhere, so I could go right, left, or up, down without letting go of the right mouse button until the data's right back on top of itself, then let go of the right mouse button. Copy Here as Values Only. So that's a quick way of throwing away the formulas and keeping the results. Now over here, we possibly want to isolate the ZIP codes. Equal right.

In this case, five characters, that's it. You've isolated the ZIP codes, copy down the column, and that's real fast, real easy. If we wanted to pull out the state, that's going to be a little trickier. Here we need to use another function, in combination with perhaps the left or the right or the mid function. So, we need to find out where the comma is, because that helps us figure out where the state's going to be. Equal find, and what are we looking for here? Within double quotes, looking for a comma.

And where are we looking for this? Over in cell D2. And we'll press Enter, and just by counting the characters from left to right, it should be the number eight. It's in the eighth position, reading from left to right. Now once we know that position, what if we pulled off the eight leftmost characters? We'd be getting, in this case, the word Boulder, along with the comma. So let's use the left function, saying, in effect, let's pull data out of cell D2, comma, and what we typically put in here is how many characters from the left hand side.

The comma was found in the eighth position, so we want to pull off seven characters. Remember, the value of this was eight, so we want to put a minus one behind that. This portion of the formula right here is equal to the number seven. We're pulling off data from the seven leftmost characters. We need a closing right parenthesis out there. Enter, we see Boulder, double-click, copying down the column. See what's happening in all these cases here. So in this case, we're nesting the functions, remember, this portion of the function finds out where the comma is.

This says use one less number of characters here. And use that as an indicator as to how many characters we extract, starting from the left hand side. So you can see how at different times you use these in combinations. Now the find function is case-sensitive, so reconstructing a different situation, let's go over here to column B. I'll delete this data. Suppose we're trying to find the letter y here. You can see it over there in the first example. Double quote y, double quote. We're looking for this over in cell A2.

It's found in the fourth position. Now what if I typed an uppercase y? That doesn't work. So be alert to that situation. However, there could be situations where maybe you had a mix, but down here I'm going to change one of the other entries to be a y, in this case, I'll put in a y, uppercase, maybe another one, y, lowercase. We're going to cover all cases here. What happens now? What if we put in lowercase? That works for here, but what happens further down? There's the uppercase y, we're unable to find it because these are lowercase y.

So in situations like that where you don't want it to be case-sensitive, use the function called search. This makes no difference what kind of y you're looking for. Wherever the y is found, upper or lower case, we do see it here. Here it's uppercase. There's a capital y in there, you see it? That was found. Here it was found. Second position, that's uppercase. But also found it up here, when it was lowercase. We could have used either one here. What if we made this a capital y and recopied, it's still going to find it.

Even though in that first case it's not capital y. So there will be times when search and find, although they're often the same, are certainly going to be different when it comes to case-sensitivity. So there are so many different techniques for using these. Here's another one off to the right. Maybe you want to isolate the names here. They're to the left of the @ sign. We need to find where the @ sign is. Double quote, the @ sign above the number two key, we're looking for that over here in G2. Where do we find it? Seventeenth position in the first case here.

So what do we want to do? Pull off characters from the left hand side of this cell, comma, not 17, but one less than that. So now I've got these isolated. If we wanted to along the same way here, we could replace eventually after doing this, we could replace the periods with a space if we wanted to do that. Or a comma and a space, that's another approach. If we wanted to isolate the data on the right side there, we could take advantage of the fact that we know where the @ sign is, so I'm just going to copy this part.

This is where we find the @ sign. Highlighting this, press Ctrl + C. Let's use that over here. Ctrl + V to paste. This tells us where the @ sign is, but now what do we want to do? We want to find all the characters to the right of it. Is that going to help us this way? What we will also will need here is another function, so let's say we found the @ sign here. This case it's in the 17th position. What's the total number of characters here? We could do this is a couple different ways. One way could be, let's say that we want to extract data from here, starting in the 18th position.

So let's use the mid function to say, looking in this data here, comma, let's start at this position. Now that's 17, but we want to start one later, 18th position, comma, but we don't know how many characters do we need. Now looking through the list here, let's say that probably 20 is a good enough maximum, but just to play it safe, put in a bigger number. How about 50 characters? So what are we saying? We found the @ sign at a certain location. Wherever we found it, let's use that as the starting point for the mid function that says, I've got some text somewhere in G2.

So starting at this position, plus one, in other words, right after the @ sign, pick up as many as 50 characters. So that number is plenty large for the circumstances we've got here. We'll do that. Now if we were to copy and paste this somewhere else, would it have trailing spaces? It might, so let's put in the word trim right here. This will get rid of all those extra spaces. So we're using these to our advantage in different ways. Here we're isolating this portion of it, maybe we doing an analysis of which mail services are being used.

So in different ways here, we've seen how to use different functions. Left and right, we saw find, we saw search, we also saw mid. There could have been a few others as well, but good enough for the situations that we've seen here. And remember, too, that shortcut. If we want to turn these into their pure values, and we could do both of these at once if we wanted to, we'll simply with the right mouse button, copy this data elsewhere. I could copy it left, right, right, left, using the right mouse button, right on top of itself, and then as we release the right mouse button, copy those values only.

So these are powerful tools for extracting and manipulating data.