Text Functions

Quick reference

• 00:04 One of the most overlooked set of functions inside Excel has to be the text functions. These things are incredibly powerful.
• 00:10 Let's take a look here and see, we've got customer numbers down here for some medical treatments and lets assume the hospital's
• 00:18 come back to us and said this is great but these customer numbers they mean something to us
• 00:22 but we need them actually displayed in a format that is
• 00:25 104-564-87. So the left three characters, the middle three characters and the right two characters with dashes around them
• 00:32 tell us something about these whether it's a region code or something about that but it's something that's important.
• 00:38 Well how do we break these things apart so we can get those particular components?
• 00:42 We're going to start by using the LEFT function, we're going to say =LEFT
• 00:45 open our bracket and refer to the particular cell. We could put in text if we
• 00:51 wanted to but we're going to go directly with the cell and say three characters.
• 00:56 Enter
• 00:57 and I'm going to run that formula down.
• 00:59 You can see that it's pulling the left three characters off of every line because we used a relative reference to set that formula up.
• 01:07 What about the right two characters? =RIGHT that's what the RIGHT functions all about is
• 01:12 feed it some text, tell it how many characters you want returned from the right, close the brackets
• 01:17 Enter, it's got 87. That's perfect, let's roll that one down
• 01:23 and we can see that it's definitely pulling the last two characters off of this. Now what about when you want to get something from the middle?
• 01:30 That's where the MID function comes in. The way MID works is we type in =MID
• 01:35 and we open the brackets and it says where is your text that you want to look at? We're going to look here
• 01:40 and it says where do you want to start? What's the first character you want to return from this particular cell? Well in this case we've already
• 01:47 got the first three we want to start with the fourth character to try and get that 5 in the 564.
• 01:52 And how many characters would you like? Three.
• 01:54 So we'll say Enter on that. Now we've got the 564.
• 01:59 Now all these records for all these customers that we pulled here from the dataset all have 564
• 02:04 as the middle code. That's ok, it does look like the formulas working to pull it out from those cells.
• 02:10 Now how do we put this back together? Well the way that we do that is we use a function called concatenate. Although we
• 02:17 are going to use the shorthand version of that because concatenate is really hard to spell.
• 02:22 So what we do is we say well we want our left characters here, left three characters.
• 02:26 Then we're going to hit the & key and this is the shortcut for concatenate.
• 02:30 Quote dash quote everything between the quotes is fed back as text. So right now we've got 104- and we're going to take MID
• 02:41 and quote dash quote, again everything between the quotes is going to come back as text.
• 02:47 And finally we can click our 87 here, hit Enter and it puts them all back together.
• 02:53 If I run that down you can see that it builds our customer numbers back in a format that's easy to understand.
• 03:00 But what happens when you actually want a look at something where you are trying to figure out where to
• 03:05 start your MID function from? Well that's what the FIND and SEARCH functions are all about.
• 03:09 They're a little bit tricky to use. So what we're going to look for is we're going to look for where transplants starts in
• 03:15 any one of these individual records. So to do that we say =FIND
• 03:21 We are going to find this text, and I'm going to set that to an absolute reference so that it always points back here.
• 03:28 And it says where? Within which text, into column B,
• 03:32 what number do you want to start at? We are going to start at the first character for our look.
• 03:36 Press Enter.
• 03:37 Great that works!
• 03:39 Lets try doing SEARCH as well just to see what happens with this one. It's important to spell it right.
• 03:44 SEARCH. We are going to search for transplant. Make that absolute again.
• 03:49 We'll go and search in column B. Going to make that relative so it carries all the way down. We're going to search from column 1.
• 03:56 And we are going to hit Enter and it comes back with the same results. Two functions that return the same results. Sort of.
• 04:04 When I run this down you are going to see something different. A couple things are worth noticing.
• 04:09 Number 1 we get a lot of value errors and the value errors are here because transplant does not exist
• 04:14 in this particular cell. That's why it's coming back with a value error so we need to guard against that.
• 04:19 You will also notice that heart transplant is showing up in both places or both columns but
• 04:24 liver transplant is not and yet the word transplant is there.
• 04:29 FIND is case sensitive.
• 04:31 That transplant has a lowercase T where this one has an upper case T so that's why that's happening.
• 04:38 Now the trick to dealing with these particular cells or these particular formulas is to guard
• 04:43 against this with an error function. So if we say =IFERROR
• 04:48 this FIND function
• 04:50 then we can return something else and we're just going to put 2 quotes, that will treat it as blank, everything between the text returns so
• 04:58 we say enter on this it still works fine.
• 05:02 Except that if it's an error it will actually blank it out. So again same thing over here
• 05:08 =IFERROR
• 05:10 We'll perform the initial test and return that value if it works
• 05:14 otherwise if it doesn't quote quote will return everything between the quotes which is nothing,
• 05:18 giving us a blank cell and when I run that down, there we go we've
• 05:22 got blank cells. So difference between FIND and SEARCH is that FIND is case
• 05:26 sensitive. Most people use search for that reason because they don't care about it but if you ever need to get something
• 05:31 that is specifically case sensitive you will want to know that.

Lesson notes are only available for subscribers.