Text Functions

Quick reference

• 00:04 In this example, we're going to look at text functions.
• 00:08 We've looked at trim and ampersand before.
• 00:10 Quick reminder of what each one of those does.
• 00:13 Trim removes or trims all the extra spaces in a string.
• 00:18 So if we apply it to A3, it will leave a single space between words.
• 00:22 It will also remove the spaces at the beginning and at the end.
• 00:27 Ampersand is the same as concatenate.
• 00:30 If I want to combine the text in column A with the text in column B,
• 00:34 I can use an ampersand, but I need a space in there.
• 00:38 So let's add an ampersand space and B5, and that looks better.
• 00:46 I can also use concatenate to combine A5, a space and B5 for the same effect.
• 00:55 And you can also just use concat, it's the same thing.
• 01:01 So the two types of text functions I want to look at in this video are ones that
• 01:06 track characters.
• 01:07 So it can tell you a position of characters in a string, or
• 01:11 can count the characters in a string.
• 01:13 And the others are, if you give it a position and a number of characters,
• 01:18 it extracts and returns a number of characters.
• 01:22 So, what does LEN do?
• 01:25 LEN of a string gives you the number of characters in it.
• 01:29 LEN of that up above is 10.
• 01:33 It's a little misleading now because I only see nine letters in the string, but
• 01:37 there's a space after the I.
• 01:39 If I go up and remove the space and hit enter, the LEN returns a value of nine.
• 01:46 The find and search functions do the same thing with one difference.
• 01:50 Find is case sensitive, but search is not.
• 01:54 So, if we want to find the lowercase c in the string above, we type find,
• 01:58 then the lowercase c in quotes, and click on the cell with the string.
• 02:04 And that will return the position of the c, which is three, third position.
• 02:10 Search does exactly the same thing.
• 02:12 Let's type in the search function and you'll see it also returns a three.
• 02:18 But just to highlight the difference here if I do a find function with an uppercase
• 02:24 C, and there's only a lowercase c in that string find will return a value error.
• 02:31 Search will work though, search will give you the position of the first c it
• 02:36 encounters, whether it's lowercase or uppercase.
• 02:40 Now here's something else we can do with strings.
• 02:43 We can extract characters,
• 02:45 meaning we can take a piece of a larger string and use it somewhere else.
• 02:50 To extract characters from a string, I can use left, right, and mid.
• 02:55 Left gets the characters from the left side of a string.
• 03:00 Let's say I want the left three characters.
• 03:04 Right takes the right characters.
• 03:08 We'll do the three right characters here.
• 03:11 For left and right, you just reference the string, and
• 03:14 then tell Excel how many characters you want from either the left or right side.
• 03:19 Mid takes characters from the middle of a string.
• 03:22 But for mid, you need to tell Excel where in the middle
• 03:25 of the string to start extracting characters.
• 03:29 For example, if you want to start at the fifth character, type in a five,
• 03:34 and then type in how many characters you want,
• 03:37 we'll say three characters again, close parentheses.
• 03:41 And in this example, we're grabbing from the string at E9.
• 03:45 We'll start at position five, the characters in positions five,
• 03:51 six, and seven, that will be E, F, and G.
• 03:54 And those are text functions.
• 03:56 They become really powerful if start to combine them to get things done.
• 04:01 Let's say for example, I want to grab three characters from that string above.
• 04:05 And I want the E and whatever the next two characters are after it.
• 04:10 But what if I don't know where the E starts, we can find it.
• 04:14 So let's use the mid function mid, click on the string, and
• 04:18 then we use the find function, find the E inside the string.
• 04:23 That'll return a five since it's the fifth character, and
• 04:26 then give us the three characters starting at that position in the string.
• 04:31 That will also give us E, F, G.
• 04:33 So, we just combined a couple of functions there and that's pretty cool.
• 04:38 Those are a few basic text functions.
• 04:40 We'll do some more with text in the next lesson.

Lesson notes are only available for subscribers.