This will be the first screencast of a two-part series entitled text functions in Excel. In this screencast, I'm going to be talking about a couple of text functions, the left function, the mid function, the right function, and then the LEN for the length. There's also find and search functions. And then real quick, I'll be talking about the upper, lower, and proper functions in Excel. We're going to be using this word here, pneumonoultramicroscopicsilicovolcanoconi- osis, something to do with inhaling silicon particles from volcanoes and causing some sort of lung ailment. This is one of the longest words in the American Dictionary. So I've got it over here in Excel, and I'm going to use this to introduce a couple of our text functions. The first function is the LEN function, and this will actually determine the length of that text. It just has a single argument, and the length of that string is 45. Now, you notice that if I go up here and I add a space and I press Enter spaces as well as other characters. So maybe I put in quotations are counted as part of that string, and so you always have to keep this in mind. Next, let's go over the left function. It actually would just give you the leftmost single character. But if you put in this optional second argument, we can determine something like the leftmost ten characters, and that would be the following. There's also a mid function, the mid will take a string and then you have two arguments. You need the start number, so maybe 20. It's going to start with character 20 of our string that's defined as the first argument, and then we can put it in the number of characters. So maybe five, so that's going to give us copic. Finally, there's the right function. This will give us the right most character. If you put in the second argument, then it's going to give us the rightmost five characters in this example, and that's iosis. Next, we have the find function. So the find function, it's again a text or string function. The first argument is what text you'd like to find and you have to put this in quotation. So if I want to find micro within this text, then I can click on that text and it tells me that micro starts at the 14th character of this string up here. Now, what if I put in a string here, no, and I want to find that text within our much longer text up here? If you look at our string up here, there's actually two incidences of N-O, we have it at the beginning, and at the end in volcano. So it's really important to realize that the find function, if you just use two arguments like this is only going to find the first of those. However, if you type in the find function again with our string and the third argument is optional as the start number. If we start searching at the previous one which is the cell above, so D7 and I add 1 to that, then it'll start searching one more than where it found the first no. We're finding a no at position 7, but then if I start with positions 7 and I add 1, that means we're starting at position 8, it'll find the second one. And then if you had a third incidence, then you would have to start searching at 37. It's important to note that the find function is case sensitive. So if I want to put in capital N-o there, it doesn't find it and it's giving us a value error. There's also a search function. So if I wanted to search for capital N-o within our string up here, it's very similar to the find function, although it's not case sensitive. So if I did that, then I can press that and it's telling me that we find that starting in position 7. Real quick, I want to show you what the upper, lower, and proper functions do. So I've got two examples here. There's a upper function that takes text and just makes it all upper case. We have a lower function that takes any string or text and converts it to lower case. And we've also got the proper function which takes if you have multiple words, it adds capital letters to the first letter of each of those words, so I could do proper of some name. And that just makes it some name with s and n capitalized. In the screencast right before this, I sort of talked about the advantages and disadvantages of Flash Fill. And one of the disadvantages is it doesn't update in a live manner, and so to make it update in a live manner we can oftentimes use text formulas or string formulas. The example here was to separate last name and first name. So let's take a closer look at what we're trying to do here. We're trying to take a last name comma first name, and separate it into last name and first name in two separate columns. So what we're doing here in this case, is we're taking the leftmost four digits. So I always going to start with the left function to get the last name, and then we have to somehow count. The way we do that, is we take a look at where is the comma? So we can use the find function to find the comma, and then we can just subtract 1 to get the number of characters of that last name. To get the first name Donald over here, it's going to be a little different because we're going to be using the right function, but we have to know in the right function the length. The key is to take the total length, so we can use the LEN function. And in this case, that would be 12 characters, and then we're going to find the comma and that's in this case the fifth position, but then we also have to account for that space. So what we have to do is we have to take 12, and then we're going to subtract 5 for the position of the comma, and we're going to add 1 for the position of space. And when we do that, that will give us a total number of characters that the first name is going to take up which is 6 in this case. So for the last name, I'm going to just type in equals. This is going to be the left function, and the text is going to be that string there, make it a relative reference, so we can drag it down. And then the number of characters, so we have to somehow determine for here, there has to be five. But what we can do is we can find, and I'm going to find the comma within that text. And then we actually need to subtract 1 because it's finding in apple comma, it's finding the comma at position six, so we subtract 1. So we want to find the leftmost five characters of that in this case, and then I can press Enter and we're finding apple. Now, I'd like to make sure this is working. So let's just drag it down a few, and it looks like it's working, and I can go ahead and double click on that to go all the way down. The nice thing about this as alluded to earlier, is I can insert here and we can put in a new student. And when I press Enter or the Tab key, it automatically recalculates and it's going to fill in that formula there, that doesn't happen with Flash Fill. So let's go ahead and delete that, and now let's talk about the first name. Again, for the first name it's a little different because we're going to be using the right function, the text that we're going to be searching through is in A2. Now, the number of characters, we have to first count the number of characters. For apple, Arnold there are 13, but I want to only use the rightmost six, so I'm going to subtract, we're going to see where we find the comma within that text. And then in this case because there's a space separating them, we need to subtract 1, so we're going to use that. And when I press Enter, it's going to extract Arnold and we can go ahead and double click on that to autofill that down. Again, this does not happen with Flash Fill. It also is nice because at the very bottom when you put in a new student, and I press Tab, it autofills those formulas down. So hopefully you learned a little bit more about using text functions. In this case, we separated text and in the next screencast, I'm going to talk about how we can join text.
