All right. I'm going to show you how we can use some of these string functions in Excel to do some different things. So, in particular, we're going to take a cell that will have last name comma first name. And what we want to be able to do is to separate out that name into the first name in one cell and the last name in an adjacent cell. So I'm going to show you how to do that. And then what we're going to do in Excel is just create another cell using built-in string functions that's going to concatenate these things with the period here and with the @ sign and some company.com that's going to be the email address for this particular employee. So, before we do this, let's sort of analyze what we're going to have to do. The first part, we start with last name, comma, first name. And we're going to use the comma as sort of our separator. In order to find the comma, we're going to use the FIND function. So we're going to find the comma within the string. And that's going to give us a position. So, in this case, for Duck comma Donald, that will give us a position of five. And then to extract the last name, so that the last name is going to be the easy part, the last name, we can just use the LEFT function. And in this case, we're going to use left of five, which is what we found, using the find with the comma, then we have to subtract one. So we're going to use the leftmost elements, four elements in this case, and we're going to use the string first and the five minus one. So, in general, we're going to put this into our function to determine the length of the leftmost number of characters, and that'll give us the last name. The first name is a little more difficult. So we're going to have to use the RIGHT function. And when we do the RIGHT function, so we're going to use our string as the argument. We are going to have to first count. So we're going to have to count the entire length of this string, including the spaces. We can use the LEN function. And then we're going to subtract then. So we're going to use the five where we find the comma, then we're going to add one for the space. So we're going to do the FIND. We're going to add one. And we're going to take that combined number, and we're going to subtract it. So this will go here. And that will then give us the number of characters on the right side of our string that we need to extract using the RIGHT function. So let's go ahead and do this in Excel. Just to kind of show you this in small steps, let's first use the FIND function to determine where that comma is. So I can use the FIND, and we're going to find the comma within our string there. So that's telling us it's up the sixth position. So then if we wanted to determine, if we want to find B, so we're going to have a first name and last name. Let's start with the last name because that's easiest. We can just use the LEFT function of our text, and we are going to then take the location minus one. In this case, we have Apple as the last name. That's where the location of the comma is six. So we only want to go one through five in this case. So that's why we subtract one. So that's how we can extract the last name. The first name is a little more difficult as I mentioned. And let me just step back. So there's going to be a length of the string. We can use the LEN function. That's the total length. So we only want to take them. For the first name, we want to take the rightmost. In this case, one, two, three, four, five, six. How do we get six from the total length? So we're going to use the rightmost. We're going to use our text. But then to get the number of characters, the six here, we take our length minus the location, but then we've subtract one for that space. So if we do that, then that's how we can create a general formula for last name and first name. So I don't know why. Let me delete that. All right. And you see that we can then, if I copy this down, then we change. It automatically adapts to wherever that comma is, and I've created a general formula in Excel to do this. If I didn't want to use these two columns, if I don't want to have these two columns of integers here, I can just take this formula. So I'm just going to copy that. And instead of cell B2 in here, I'm just going to paste. And we have to eliminate that equal sign. All right. And I can do the same thing here. For where we had B2, I'm going to paste that formula that we made in cell B2. We still get the same thing. But now, I don't even need this. The length then, instead of using C2 here, I can just take the length of A2. So I'll say, C2. We're going to take the length of A2, and then we don't even need the C column. So now I can go ahead and delete those, and I can copy this down, and it works for any combination of last name comma first name. Now, I'm going to show you how we can create the email address. And we wanted to make it so it's lowercase. So it's going to be firstname.lastname@somecompany.com. So I'm going to use the CONCATENATE function in Excel. We're going to do first name, but we want to make this lowercase. So I'm just going to use the LOWER function. So, lower of the first name. We're going to concatenate that with a dot that separates the last name and first name. We're also going to concatenate that then with the lower of the last name. We're going to concatenate that with the @ sign. We're going to concatenate that. I guess I can put this all in the same string here, @somecompany.com. So that's how then we can get the email address. We can take last name, first name, make it a firstname.lastname, make that lowercase, and then I can copy that down. In subsequent screencast, I'm going to show you how we can do this type of thing using the string functions that are built into VBA. Thanks for watching.
