Text Functions

Quick reference

• 00:04 In this video we're going to look at text functions.
• 00:08 These are functions that a lot of users miss in their education because a lot of
• 00:12 people think Excel is all but working with numbers, and it's absolutely not.
• 00:16 The way these functions work is they can work with text or numbers, and
• 00:20 I'm gonna show you with UPC codes on the left.
• 00:22 This is Universal Product Code for
• 00:24 a specific product is also with The item names themselves.
• 00:29 So we're gonna start right now by pulling off the left three characters
• 00:32 off of the item name.
• 00:34 To do that, we use the LEFT function, and
• 00:36 it says what's the text that you would like to supply?
• 00:38 So we'll click on Kraft Singles Cheese, and
• 00:41 how many characters would you like to return?
• 00:43 We will say 3, close the brackets and hit enter.
• 00:46 And you'll notice it comes back to KRA
• 00:49 which makes sense because it's the last three characters.
• 00:52 So as we drag that down we can see that it automatically
• 00:55 pulls off the last three characters on any of these pieces of text.
• 00:59 I wanna point out though that this can also be used with numeric fields as well,
• 01:03 like the UPC codes.
• 01:04 So if I wanted to break this UPC code into three different parts,
• 01:08 here's what I would end up doing.
• 01:09 I would say equals left, open brackets, click on the UPC code and say comma three.
• 01:17 And now we'll hit Enter, and we'll drag this or
• 01:20 fill this all the way down the table.
• 01:22 Oops, I missed on that one.
• 01:24 Double click, there we go.
• 01:26 So we can see that the left three characters in every instance is 468.
• 01:30 Now the right function allows us to pull items from the right hand side,
• 01:35 whether it be from text or numbers.
• 01:38 So we can say ='s right, open brackets, then we'll go and
• 01:41 pick off the right three characters off of this UPC code as well,
• 01:47 and I'm gonna hit command enter to commit this to all the cells at the same time.
• 01:51 And you can see that we now have four three five and zero one five and seven two
• 01:55 seven, all those individual characters are coming down the side of our column here.
• 02:04 The mid function is another really useful function, and
• 02:06 what this one does is it actually pulls back characters from the middle of
• 02:10 the text string whether it's actually text or whether it's a UPC code.
• 02:16 So in this particular case here, I'm gonna say equals mid.
• 02:19 And mid takes an extra parameter.
• 02:21 It says what's the text you'd like to look at.
• 02:23 So I'm gonna go and start with the UPC code.
• 02:25 Now what I'd like to do is pull out the middle
• 02:28 two characters in this particular case.
• 02:29 So in this case, 32, so it says where would you like to start?
• 02:34 Well I'd like to start with the number 3, which is the fourth character.
• 02:38 four, six, eight, three is the fourth character so we'll say start four.
• 02:42 So then it says how many characters would you like to extract here.
• 02:46 When you'll say two, and I close my brackets and hit enter and
• 02:50 you'll see that picks up 32.
• 02:51 So I can actually pull out the individual
• 02:55 piece in the middle of this particular string of texts.
• 02:58 This is really useful if you've got codes that have leading certain numbers.
• 03:03 And then our product code is actually the next you know sequence of numbers.
• 03:06 Or if you're looking for a specific character within a string of texts,
• 03:09 like an address.
• 03:10 Both those cases work quite well.
• 03:13 Now, another piece that we can do, this is the text functions are really useful for
• 03:18 tearing text apart, but they're also useful for putting text back together.
• 03:22 And the easiest way is to do something like this, where we see equals and
• 03:26 we'll click on the left character.
• 03:27 And then we'll put in B Ampersand or the and character.
• 03:31 I'm gonna put "-", so everything between quotes is treated as text.
• 03:36 Then I'll say &, I'll put in my mid, I'll say
• 03:42 & "-" & the right character, or right pattern that I pulled off before.
• 03:48 So, basically,
• 03:49 what's happened now is I've actually injected some hyphens into the middle of
• 03:53 my product code here, because maybe this patterns means something specific to me.
• 03:58 Maybe I've got 4, 6, 8 as just my general for my company,
• 04:01 32 is the division and this is their individual product number at the end.
• 04:05 That could be a pattern that's actually built out of this particular
• 04:08 code in some cases.
• 04:10 Next, I'd like to look at the Find and Search functions.
• 04:13 These functions are used to look up a specific piece of text within other text.
• 04:17 So the way we're gonna start here is we're gonna go with Find and we're gonna try and
• 04:22 find the specific piece of text, cheese.
• 04:24 And I'm gonna make this absolute so I can copy this formula around, so
• 04:27 it's anchored here.
• 04:29 And where are we gonna look for this?
• 04:31 Well, we're gonna look for this inside the cell's item name.
• 04:35 And we can provide an optional start number,
• 04:37 I'm gonna ignore that from now to start from the beginning.
• 04:39 And we'll say, Enter.
• 04:40 And it says, 15.
• 04:41 Why?
• 04:42 Because that's the first character where Cheese actually starts.
• 04:46 Now, here's the kicker with the find function, though.
• 04:49 As we carry this down the column you'll notice that it returns a bunch of
• 04:52 value errors.
• 04:53 And basically what that means is that it can't find cheese.
• 04:56 But this is not really a good good format, is it?
• 04:58 It would be much better if we could actually wrap this to protect and
• 05:02 return just a blank instead.
• 05:04 The way we do that is we preface our find function with if error, open brackets.
• 05:10 If error will run the original test and if it works, it returns that value otherwise.
• 05:15 I'm gonna put in quote, quote.
• 05:17 And that's going to feed back everything between the quotes as text.
• 05:21 And because there's nothing between the quotes, when we hit Cmd+Enter,
• 05:24 you'll notice that these cells still have their formula, but
• 05:27 they return blank because cheese couldn't be found.
• 05:29 But wait, you say.
• 05:31 There's cheese right here.
• 05:32 Why is it not finding it?
• 05:33 Well this is the problem is that find is case sensitive.
• 05:36 So for that reason we also have the search function and search is not case sensitive.
• 05:42 So we can say find and we'll go with F2,
• 05:46 within our items close the bracket.
• 05:52 We'll hit command enter.
• 05:53 and you'll notice that it returns okay for cheese.
• 05:57 But it returns a value error for anything where it couldn't find it.
• 06:00 So in this case again, we'll go back.
• 06:02 And we'll actually wrap our search functions
• 06:06 again with IFERROR to guard against that particular problem.
• 06:10 So, let's see, IFERROR, brackets, if we do have an error let's return quote,
• 06:16 quote, so everything between the quotes which is nothing.
• 06:18 Cmd+Enter, and
• 06:20 you can see that we now find cheese where it exists in our product numbers.

Lesson notes are only available for subscribers.