Text Functions

Quick reference

• 00:05 So far we've focused on mathematical formulas as we've been going through
• 00:09 this course.
• 00:09 But now we're going to focus on some text functions.
• 00:13 These functions will allow you to extract or
• 00:15 find certain pieces of text in a string of text.
• 00:18 So the first one we'll start with is equals left, and
• 00:22 I'm going to demo first on my items here with text here and say, you know what?
• 00:27 Let's pull back the left five characters and
• 00:31 you'll see that it returns in this case craft and if I were to drive it down here
• 00:36 it's got a few different things that it's actually pulled off.
• 00:39 So if we look at Minute Maid, it's actually missing the e on minute because
• 00:42 it's only pulled the left five characters.
• 00:45 But what's interesting about this function is it can also be used to go back and
• 00:50 actually pull off say, the left three characters off of a number.
• 00:55 So, our UPC Code.
• 00:59 And, we'll hit Enter, and we'll draw that down.
• 01:02 You'll notice that it's pulling off 468 off of every UPC Code that we have here.
• 01:08 We could also go and say,
• 01:09 you know what maybe I wanna pull of the right number of characters from something.
• 01:14 So I can grab this guy here and say, give me the right three characters, and
• 01:19 it'll return 435 in this case.
• 01:22 And a different number all the way down but
• 01:24 every time counting in from the right to pull the last characters off.
• 01:29 Now, another really useful function is the mid function.
• 01:32 And what the mid function does is it allows us to actually provide a piece of
• 01:36 text and say, which character would you like to start extracting your text from?
• 01:43 In this case let's grab the fourth character.
• 01:45 And then it says, how many characters would you like me to return?
• 01:49 I'll say, you know what, let's grab two.
• 01:51 And if we hit Enter it'll pull out the 32 from the middle.
• 01:56 Something else that's interesting to know about mid, is that if we provide a number
• 02:00 that is larger than the number of characters remaining, we could actually
• 02:04 use it to pull off all characters from the right of wherever we start.
• 02:10 So, in some respects, you could actually if you wanted to replicate
• 02:16 the right function results by using your MID function,
• 02:20 although in this case, it probably doesn't make a lot of sense to do so.
• 02:24 But instead, let's go back to MID and
• 02:27 we'll take our text, start at the fourth character, and return to.
• 02:34 This can be useful if for some reason I need to break apart my code and
• 02:38 actually build it back together with say, some dashes.
• 02:42 The way we can do that is, so far we've just extracted text but we can actually
• 02:46 put it back together, concatenate it using the and character or ampersand.
• 02:51 So, we'll say equals left.
• 02:54 We can say and, and remember anything between quotes gets returned as text.
• 02:59 So we'll say quote dash quote and.
• 03:03 We can hit mid.
• 03:04 We can say and quote dash quote and
• 03:09 the right character and we've now managed to take our number and actually build it
• 03:13 into something that is a little bit easier to read with the dashes in place.
• 03:18 And you'll notice that we've actually pulled all these things apart and
• 03:20 put them back together in exactly the same way.
• 03:24 So this is helpful because we can actually extract the left, the right or
• 03:27 even something for the middle and we can put it back together.
• 03:31 Sometimes though we need to figure out,
• 03:32 where do we actually break in to use something like the mid function?
• 03:36 And for that reason we have something called Find and Search, and
• 03:39 these functions are similar and yet different.
• 03:42 So the way this works is,
• 03:44 it actually allows you to find a specific piece of text.
• 03:47 So we'll grab cheese here, and I'm gonna hit F4 to make this absolute.
• 03:51 The F4 key will toggle us through the absolute and
• 03:54 relative states of our formula.
• 03:56 Okay so there we go, well press F4 and make dollar J, dollar 2 absolute so
• 04:00 we copy this all the way down the column here and
• 04:04 it says that's the text you want to find, where is it?
• 04:07 Well it should be in this cell.
• 04:10 Close our parentheses and hit Enter, and
• 04:12 it says we found cheese starting at the 15th character.
• 04:16 No problem, challenge is when I roll this down.
• 04:20 It gives me value errors because it can't find cheese and you say,
• 04:24 well wait a minute.
• 04:25 I get that on this stuff down here but what is going on here?
• 04:28 The cheese. This is Cracker Barrel cheese.
• 04:30 The problem is the find function is case sensitive.
• 04:34 It doesn't recognize lowercase Gs because I asked for uppercase Gs.
• 04:39 So, in this case what we need to do to protect ourselves against results this
• 04:44 way is we need to wrap our function in something called if error.
• 04:49 And what if error will do, is it will run the original function.
• 04:53 If it works, it'll return that result.
• 04:55 Otherwise we can return a different value if there's an error.
• 04:59 And we'll return quote, quote.
• 05:01 Because everything between the quotes will be returned,
• 05:04 there's nothing between the quotes or nothing gets returned.
• 05:07 Hit Enter, and that's how we actually protect
• 05:11 against these kind of errors showing up in our formula.
• 05:14 Search is very similar to what we have.
• 05:17 It returns errors just the same.
• 05:19 So we'll say IFERROR right off the bat.
• 05:21 And then we're gonna do a search function.
• 05:24 We're gonna look for cheese.
• 05:25 Again we'll make this absolute.
• 05:28 We're gonna look over here in the Kraft Singles.
• 05:32 Close our parenthesis on the search function and
• 05:36 then alternately it will return quote quote nothing if this returns an error.
• 05:41 When we hit Enter, it returns the same as the find function here but
• 05:46 as we take it down, you'll notice that Search, unlike Find,
• 05:50 is not case-sensitive, and actually picks up the lowercase cheese, as well.

Lesson notes are only available for subscribers.