Text Functions

Quick reference

• 00:04 Now, when most people think about working with Excel and Excel formulas,
• 00:07 they think about numbers.
• 00:09 But the reality is, Excel actually has a collection of text functions that can be
• 00:13 very useful for extracting and putting data together.
• 00:16 So let's start here.
• 00:17 We're going to start with a function called left.
• 00:19 And what left does, is it takes, the first parameter is some text.
• 00:24 And then it says, how many characters would you like me to return from that?
• 00:28 So I'm going to take 5, and
• 00:29 it will return the left five characters from the text string.
• 00:33 So in this case Kraft.
• 00:34 And if I move this down one row from Cracker Barrel cheese,
• 00:37 we're going to get Crack.
• 00:38 And if I roll and scroll this all the way down, you can see we got Dairy, Minute,
• 00:43 Demp, Schne, which is kind of a little bit weird.
• 00:45 And on the very bottom we have eggs.
• 00:47 Notice that we asked for the first five characters, that weren't five,
• 00:51 so it only gives me four for eggs.
• 00:53 Fair enough.
• 00:55 Let's go ahead and take a look at the right function now, equals right.
• 00:59 Again, we're going to go with Kraft Singles cheese here.
• 01:01 And this time I'm going to go with six, and that should give me cheese.
• 01:06 And now when I roll this down, what do we get?
• 01:09 We get cheese, percent, milk, juice, bread, some ey ham, some arrots, and
• 01:13 again, eggs.
• 01:14 Notice that we asked for six characters.
• 01:17 It looks from the right hand side, it says, hey, there's only four.
• 01:20 I'll give you all the four I got.
• 01:21 No problem.
• 01:23 The next function here, which is super, super useful, is called mid.
• 01:28 And what mid does, is it allows us again to feed it a piece of text, and
• 01:33 then says, what character would you like me to start returning a result from?
• 01:38 And I'm going to say, give me the seventh character.
• 01:40 That's going to be the first character I'm going to return.
• 01:42 And then how many characters would you like me to get?
• 01:44 Well, I'll say seven.
• 01:46 We're going to close the parenthesis on this and hit Enter.
• 01:49 And at this point it gives me Singles.
• 01:51 S is the seventh letter, and Singles is seven characters long,
• 01:55 which is why I chose those numbers.
• 01:58 What happens if I roll this one down here?
• 02:00 Well, we get an r Barre, and 4 leader, and Maid O.
• 02:04 So we get some strange things for sure.
• 02:06 But notice that when we get to eggs, well,
• 02:10 there aren't seven characters, so it returns nothing for us.
• 02:15 Fair enough.
• 02:16 Now, this is interesting, but how can we use this?
• 02:18 Well, sometimes it's helpful when we want to put text back together.
• 02:22 So I want to take this UPC code here, and I want to split it into three components.
• 02:25 So I want 468-32-435.
• 02:29 So here's what I'm going to do.
• 02:31 I'm going to go and say, give me the left three characters from this,
• 02:36 and then we're going to use and.
• 02:39 And we're going to feed back a piece of text.
• 02:41 And I want everything between the quotes, that's the secret to getting text here.
• 02:45 And then we'll say and again.
• 02:47 And now I'm going to say mid.
• 02:49 And we're going to go and look at the UPC code again.
• 02:51 We're going to start at the fourth character and grab two characters.
• 02:55 And again feedback everything between the text.
• 02:58 So again a quote, and then a dash and a quote.
• 03:01 And finally, I'm going to go with the right.
• 03:04 And we're going to pick this guy up again, and use the right three characters.
• 03:09 And when I go and put enter on this, we get 468-32-435.
• 03:15 And if I run this all the way down the table, you'll notice that I've now
• 03:19 split up and put back together my UPC code with the formatting that I want.
• 03:24 That's just one way to do this kind of job.
• 03:26 The interesting part about this, is you may know another formula for
• 03:31 this if you've worked with Excel for a while, called concatenate.
• 03:36 The thing that you want to realize about concatenate though,
• 03:39 is that this one here is provided for compatibility with Excel 2007 or earlier.
• 03:44 It is way easier to spell, and then concatenate.
• 03:48 So I highly suggest that you use this kind of a version.
• 03:53 Now, let's talk about this one here, find.
• 03:57 What is find all about?
• 03:58 Well, mid is great if you know exactly what numbers you want to look for.
• 04:02 But what if you want to look for a specific set, or a specific character and
• 04:05 where it starts.
• 04:06 So you can feed that and use it in place of one of these parameters.
• 04:09 And this is one of the features, one of the functions we can use to do this.
• 04:13 So here's what I'm going to do.
• 04:14 I'm going to hit Find.
• 04:16 And what we're going to do, is we're going to find the word cheese.
• 04:19 And it says, all right, what text would you like to find it in?
• 04:23 I'm going to look inside the item number.
• 04:26 And at this point I have an optional start number, but I'm going to leave that alone.
• 04:29 I'm going to close the parenthesis, and hit Enter.
• 04:32 And it says, I found cheese starting at character 15.
• 04:35 So if I needed to use the mid function to extract a specific word,
• 04:39 I could potentially use the find function to figure out where it is,
• 04:43 so now I know where to start extracting things for the second parameter of mid.
• 04:48 But there's a challenge with find.
• 04:52 If I roll this down, you'll notice it gives me a value error.
• 04:55 And there's something that's really important to understand about this one.
• 04:58 If you can't find the word it returns an error.
• 05:01 Well, that's not awesome.
• 05:03 So let's fix this.
• 05:05 We're going to wrap this in a function called if error.
• 05:10 And what if error does, it says, tell me the value you want, and then,
• 05:15 what do you want me to do if there's an error?
• 05:18 I'm going to feed back the text between the quotes here.
• 05:21 So quote, quote, there's no text between the quote, so it gives back a blank.
• 05:25 CTRL + Enter to enter it in.
• 05:27 And you'll notice that we still found cheese, but
• 05:31 here we did not find cheese, and yet cheese's in this one.
• 05:35 So why is this not showing up?
• 05:37 And the answer is, find is case sensitive.
• 05:41 Search, however, is not.
• 05:44 So let's go and take a look at search.
• 05:49 We'll search for cheese.
• 05:51 We're going to go and look inside Kraft Singles cheese, close the parenthesis,
• 05:56 and roll this down the table.
• 05:58 And we've got this whole issue with value errors again.
• 06:02 This is something I like about mid.
• 06:03 If it doesn't find text, it returns a blank cell.
• 06:06 I wish find and search to do the same, but they return these errors.
• 06:09 So what we're going to do, is once again, we're going to find our search.
• 06:12 Right after the equals we're going to type in, if error.
• 06:15 And then we're going to move to the end of our search, which we know is
• 06:19 already working because we can see it on the first two lines, comma, quote quotes.
• 06:23 Again returns everything between the quotes.
• 06:26 And now I'm going to hit Enter, and I'm going to copy this down.
• 06:30 Notice the difference between find and search.
• 06:33 In this case, even though cheese is capitalized here, it's lowercase here,
• 06:38 it still picks it up.
• 06:39 In this case, if I were to change my cheese to be lowercase cheese,
• 06:43 notice the final result picks it up here, and yet this one gets blanked out, okay?
• 06:48 So keep that in mind, these are case sensitive.
• 06:52 The big cool piece about these formulas, is that now you know find and search,
• 06:56 you know and, and how to put things back together.
• 06:59 You will now be able to master the control of your left, right, and mid, to extract
• 07:03 the exact number of characters you want by putting all these formulas together.
• 07:07 And that is awesome stuff.

Lesson notes are only available for subscribers.