Focus video player for keyboard shortcuts
Auto
- HD
- 720p
- 540p
- 360p
1.00x
cc
- 0.50x
- 0.75x
- 1.00x
- 1.25x
- 1.50x
- 1.75x
- 2.00x
We hope you enjoyed this lesson.
Cool lesson, huh? Share it with your friends
About this lesson
In this lesson, we learn how to use LEN, FIND, SEARCH, LEFT, RIGHT and MID.
Quick reference
Text Functions
Learn the basic uses of LEN, FIND, SEARCH, LEFT, RIGHT and MID
When to use
These functions are useful to find or extract parts of text from a larger text (or string).
Instructions
- LEN - counts the number of characters in a string
- FIND - will find a certain character in a string and is case sensitive
- SEARCH - finds a certain character in a string and is NOT case sensitive
- LEFT - extracts a certain amount of characters starting at the left of a string
- RIGHT - extracts a certain amount of characters starting at the right of a string
- MID - extracts a certain amount of characters from a designated character in a string
- 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.