LEFT, RIGHT, and MID

- [Oz] Here we go. I'm going to show you left, right, mid. They can be very basic in what they do. And then you might ask me, "Oz, what is the point of this?" Well, we are here in advanced functions and formulas. And by the end of this video we will get pretty advanced. So, but let's look at this, the very basics. We've got these codes in column B and this is how we want to split 'em out. We see part one, part two, part three and we're going to use left, right, mid to peel these down. Now, first of all let's do one thing. I'm going to go, equals, len, open parentheses to make sure that all of these do have the same number of characters. Okay, so 12. Double click, boom. All of 'em do have 12, see? You just learned something new. You got the len function. All right, now we see that there is consistency in this data. To get started, I'm going to get rid of that. Okay. Next, I'm going to put this data into a table. Format it as a table. Let's grab the black color, and Okay. Column has headers. Get rid of the filter buttons. Okay, let's get going. Equals, left, open parentheses. Left of which text? The code. Comma. How many characters? Our first split should have 2 characters. Close parentheses and Enter. Great, we got the first two out. Next we need the A12014. Okay, that's going to be mid. Equals, mid, open parentheses. And then the text. We want to start at the third character. So comma, start number, start at 3. That's the first one we want to grab. Comma. And in this pattern we need to grab 6, because that's the portion of the code that we want. Close parentheses and Enter. We've got the second part. And then the third part, let's go over here. We want to do right. Equals right, open parentheses. Text, the code, comma, how many characters do we want, starting from the right? We want 4. And close parentheses, and Enter. Now everything is all split apart. Now, let's look at products. Now this gets a little sneaky. We've got Orange, PQL3, and CC. We've got product, style, and color that we want to split out. I'm going to delete these. Delete. And I'm going to put the data into a table at home. Go to Format as Table. Grab this purple table. Does have headers. Okay. Get rid of the filter buttons. The easy one to grab is the style. Equal, right. Okay, we're starting at the right end. Right of what text? That text, comma, we want the right 2. And Enter. Let's grab the color. Right, now you see we've got orange, blue, metallic purple, red-orange. They're all over the place. But we do have a pattern. We need to split off the last 6 and keep the first how many ever. So I'm going to go to color, equals. And then len, open parentheses. Enter. Now we have the number of characters in each product code. Now I'm going to go back to this cell. Go to the formula bar. Minus, 6, Enter. Okay, okay. Now, let's go to the front. Okay, we're going to the left. Left, open parentheses. Left of which text? Go here. Comma, the number of characters is already done. Go to the end, close parentheses, and Enter. Look at that. Double click. Move these hands out of the way. We got it. We had to use len. We had to be really crafty about how we peeled that apart. Now, how are we going to get the product? This is how we're going to get the product. Equals, len, open parenthesis. The color, Enter. So now I want to use mid to peel out the 4 that represent the product. So in order to start on that first character, I need to add 1 to this length. Go up here and then, +1. Got it. All right. Next I'm going over to the beginning of the formula. Mid, open parenthesis, text. This text. Comma, start number. See we already have it. And then go to the end. Comma, we know it's 4. 4 and Enter. Okay, missing the closing parentheses. Not a problem. Close it and then Enter. Unh, look at that. Double click. Yep, we got the product, the style, the color, all split out because you and I are crafty and smart. Now let me show you one thing. I'm going to grab this. I highlighted, now I'm going to copy. Now paste, because I want to be honest with you. Watch this. We have Flash Fill in Excel. So I'm going to go CC right here. YH. Look at this. Hit enter. Did you see that ghost? And that was Flash Fill. I'm going to highlight here and go to Data, and then hit this lightning bolt. Flash Fill. Okay, so it has split out the KT from PinkPSZ2KT. That's good. We can do this. Orange and blue. Look at that, it's ghosting. Here we go. Double click. We got the color peeled out. We could have skipped the len, the left, right mid, etc. and just use Flash Fill. But that's when Flash Fill can find a pattern for us. It's good for very specific uses. And if you have a whole lot of data and if you have any kind of inconsistencies, Flash Fill can either bomb out, or it'll give you the wrong information. So I'm going to get rid of this data. I just wanted to show you that to be honest. 'cause somebody might be saying, "Oz, why didn't you use Flash Fill to do that?" Well, I'm teaching left, right, and mid. That's one thing. Okay, now check this out. Huh? Zoom in. We've got EELAS. LAS. We look over on the right side. That's Las Vegas airport. Okay. EE. Let's scroll down. EE is is Eladio. All right. So we want to get the name and the airport. Let's do the airport first. Equals, right, open parentheses. Text. It's there. Comma. All of the airport codes have three characters. Enter. Now watch this. Equals, X lookup. See, I'm putting the right inside an X lookup. Here we go. Double click, the parenthesis is open. All right, so it's looking up the right three of cell B2, the LAS. Comma, look up array. Where do we want to look it up? We want to look it up at this airport column, right? Comma, and return, what? We want to return the city. Okay. Comma, if not found, this is what I want. I'm going to hit the Windows key + period. I want these eyeballs that say, look, 'cause something is wrong. And that has to be in double quotes. Double quote. Okay, so that's if not found. We don't need a match mode or a search mode. I'm going to hit Enter. Okay. I do need my closing parentheses. Yes. Okay. And then send this down. Okay. Ah. So we do have something odd. Okay. Looks like we don't need EEN. This probably should have been DEN. Alright, so let's get rid of that. D, Enter. Alright, now you see the issue I was bringing up about if there's any inconsistencies in your data, any corrupt data, Flash Fill might give you something odd. And we just saw that. Okay, so now we've got the airports. Let's get the names. Here again, we're going to len. Equals, len, open parentheses, the text, Enter. And then we want, go back there, back to C2. Len. All of the airport codes are three. So we want len -3. Enter. Go back to C2. We want left, equals, left, open parenthesis. Left of B2, comma, the number of characters, that's what we did with our len -3, and close parentheses, Enter. EE. And then we're going to put this into an X lookup. Equals, X lookup. Double click. We've got the open parentheses, we've got our lookup value. Go to the end, comma, lookup array. Look for the codes where? look 'em up in this ID column. Comma, return array. Return the name, comma, if not found. Windows key + period. The eyes. Okay. And double quote. Double quote. Okay. That's the if not found, and then Enter. Yes, we need to close parentheses. Okay, go back up. That's a Eladio Espinoza. Double click to send it down. Hmm. We've got these eyes telling us to look at this entry. There's an ELAS. There's something odd going on. And this is where we may be able to figure this out and fix it. We might not. But let's look down and see an EL. Well, we've got ELAS, so we really have just an E, assuming that the LAS really means Las Vegas and we don't know if we are dealing with E for Earl, Eladio, or Evan, or maybe even Marie or Melvin. We don't know. And we would just have to leave these eyes here until we can figure out what happened. Okay, that's left, right, mid, for advanced functions and formulas.