Text Functions

Quick reference

• 00:04 So far, all of the formulas and functions we've been working with have either been
• 00:08 mathematically or statistically based.
• 00:11 So why don't we see if we can work with some text and
• 00:14 to demonstrate that we're going to start with the LEFT function.
• 00:17 So I'm going to go with equals LEFT.
• 00:19 And I'll hit Tab and you'll notice it asks me for some text.
• 00:23 So I'll say let's take Kraft Singles Cheese and return the first 5 characters.
• 00:29 And as you might expect, it comes back with the word Kraft.
• 00:32 So it's not all about numbers to Excel, we can work with text too.
• 00:36 And if I roll this down, you'll notice that we can see words Crack and Dairy.
• 00:40 Minut with no e because when we look at Minute Maid
• 00:45 it's actually counting only the first five characters so it drops the e.
• 00:49 What if we wanted to return the right 5 characters?
• 00:52 Well, we would say equals right.
• 00:55 We would point to our piece of text ,5 and close our parenthesis.
• 01:00 At that point we get heese, again, it's the right five characters.
• 01:06 As I go one and roll that down, you'll notice that I can
• 01:09 actually get a whole bunch of things, some words that make sense and
• 01:12 some that don't, based on how many characters we've actually split apart.
• 01:17 What about mid?
• 01:18 Well, the mid function allows us to go and
• 01:21 grab text from the middle of a text stream.
• 01:23 So if I go and say let's go and grab Kraft Singles Cheese, and why don't we
• 01:29 start at say the seventh character, and go and return say seven characters, why not?
• 01:35 Just to be different, we'll hit Enter and
• 01:37 you'll notice that we get the word Singles.
• 01:40 And yet, when I drag it all the way down, I get some very strange things because
• 01:44 it's always starting at the seventh character and
• 01:46 pulling seven characters across.
• 01:49 So this is some strange stuff to see, but it'll get a little bit more sensible once
• 01:54 we can actually start to search for characters.
• 01:56 But before we do that,
• 01:57 I want to show you how we can actually put text back together.
• 02:01 When we've actually broken the part.
• 02:03 And for this one, I'm gonna actually start with equals LEFT, and
• 02:07 this time I'm gonna use the UPC code.
• 02:10 Notice, this is a number, but I'm gonna extract the first three characters
• 02:13 off of this particular number, so 468.
• 02:16 And then gonna join this with some more text by using the ampersand character.
• 02:21 And to join text it has to be between quotes.
• 02:23 So we'll go quote dash quote and percent it to join it to the next piece.
• 02:28 I'm gonna say MID and I'm gonna grab the PC code again.
• 02:32 We're gonna start a fourth character and return next two characters.
• 02:37 And quote dash quote, and we're now gonna go with
• 02:42 the right three characters off of the UPC code as well.
• 02:46 So basically, what I'm trying to do here is I'm trying to actually break this apart
• 02:51 to show hyphens inside the actual UPC.
• 02:53 I've pulled all of the characters because I know it's always the same length.
• 02:56 And at this point, I can now build by extracting and putting information
• 03:01 back together, a more readable format of my UPC code which is pretty neat.
• 03:06 Now one of the challenges of working with this is if I wanted to put information
• 03:10 like this back together, I would have to search for different characters.
• 03:13 So what if I wanted to extract certain components and I was looking for
• 03:17 the word cheese up here.
• 03:19 Well, this is what the find function is all about.
• 03:21 We're going to go and say equals find.
• 03:24 And it says what text would you like to find?
• 03:26 We're gonna grab cheese and
• 03:28 am gonna make it absolute by pressing the F4 key because as I roll this down all
• 03:32 the rows I want this to always to be look at that particular cell B5.
• 03:36 And it says where are you going to look for your cheese?
• 03:40 Am gonna say, let's look until B8.
• 03:43 Optionally, if I know that there's multiple cheeses in this particular cell
• 03:47 I could feed it a starting number to say I want you to start at character 14 so
• 03:51 that it only looks at a certain piece.
• 03:53 But right now, that's not really necessary.
• 03:56 So I'm gonna close my parenthesis and hit Enter.
• 03:59 And notice that it tells me 15, why 15?
• 04:01 Well, if you count every character, including the spaces,
• 04:04 you'll find that the C in cheese is the 15th character.
• 04:09 So that's pretty neat.
• 04:10 Because if I wanted to extract the right or mid, let's say mid.
• 04:14 I could actually now use that to say start at character 15 by looking for cheese.
• 04:20 What happens when I now go and roll this down?
• 04:23 Well, I get value errors.
• 04:25 And if we look at the third row, you'll see that in Dairyland 4 liters 2% milk
• 04:29 there is no cheese, and therefore it's returning a hash value error.
• 04:33 So let's fix this, and to do it, we'll use the if error function.
• 04:36 The if error says, what's the value that you're looking for?
• 04:42 If it works,
• 04:43 it'll return the value, if it doesn't we get to alternately declare something else.
• 04:48 And in this case I am going to put in double quote, double quote two of them.
• 04:54 When I close the parentheses this will return everything between the quotes
• 04:58 to the cell.
• 05:00 Which in this case is 15 because the formula worked, but
• 05:04 if I roll this down everything between the quotes,
• 05:06 where it didn't work, is nothing, so it returns nothing to the cell.
• 05:11 So why do I have another function over here?
• 05:13 Well, equals search allows me to do similar things.
• 05:17 I can search for cheese, we'll press F4 comma,
• 05:22 I can search in Kraft Singles Cheese and
• 05:25 close the parentheses, just like I did with the fine function.
• 05:28 And just like with a fine function, it returns 15.
• 05:31 Now we know it's gonna work the same as fine, so
• 05:34 let's just wrap this in an if error way.
• 05:37 So we'll go with if error, we'll then search.
• 05:40 If it works, return that value, if it doesn't,
• 05:43 return quote quote, get the mouse out of the way there.
• 05:46 So two sets of double quotes, close our parenthesis.
• 05:50 I'll now roll this down and
• 05:52 this will highlight the difference between these two things.
• 05:54 The difference is the search function is not case sensitive, so
• 05:58 it picks up the lower case cheese,
• 06:00 where the find function is case sensitive, so it picks up the upper case only.

Lesson notes are only available for subscribers.