All right. I'm going to go through an example where we are going to extract the e-mail address, you just want to extract only the e-mail address. There are four different formats on that worksheet that I just showed you. This first format, we just want to extract arnold.apple@gmail.com. The second format we have last name comma first name and we just want to extract the e-mail address. There's another format where there's no change necessary, and then there's a final one where we have first name, last name, comma email address. So the first step in this approach is to find the location of the at sign. This whole thing here is going to be a string that I'm going to call s and we're going to use the find function to see where the at sign is and we're going to call this parameter atLoc which is going to be an integer. Starting from the at sign, we're going to iterate backwards. So maybe this is I don't know what this is. Maybe that's 25 and we're going to go down by one. So we're going to go 25, 24, 23 and we're going to search until we find a colon, a less than or a space. If we back up all the way to location one, then that means that we're working with this third scenario there. We're going to go backwards until we either find a colon, a less than, a space. Once we find the colon or the less than or the space, we're going to go one forward and we're going to name that start location. Then we're going to start from the at sign, so that's again the atLoc, and we're going to start from plus one which would be the g in this case and we're going to go until we either find a greater than sign or a right square bracket. The end location is going to be defined as i minus one, so once we find the square bracket, we're going to back up on and that's going to be the end location. Finally, we're going to output the string starting at start location with length end location minus start location and we have to add one. We're going to be doing that with the mid function. So if this whole thing here is little s, then we would use the mid function s and we would use start location and then this, the length, would go as the third argument. So what we're going to do is we're going to create a user-defined VBA function called Email and I can just reference then the mixed form, and it's going to extract only the email address and you can see that it works on a variety of different types. So the first thing I'm going to do is I'm going to make my function. It's called Email. It has an argument s which is going to be a range and then the email function is outputting a string. I need to dim L as an integer. L then is defined by the length of s. I'm also going to dim the at location as an integer, and the at location we can get using the instring function and we're looking through s for the at sign. Next, I'm going to implement a for loop and I'm iterating over i so I need to add i as an integer. We're starting at the at location minus one and we're going to step down by one until we reach one and this is where we're going to be searching for the different things that define what we're looking for. So for example, we're going to start at at location minus one. So here, we have our at location, we're going to start at the e in this scenario, we're going to go down by negative one until we either hit a colon, a less than sign, or a space. Or if we go all the way to one, that's when we're going to stop. So we're going to iterate for i equals at location minus one and we're going to be doing a conditional statement here. So we're going to use the mid function. If the ith component, we're using one as the length, so if the ith component of s is equal to colon or is equal to less then sign or is equal to a blank, then that means we found that item, sort of the end value and we're going to then say the start location is i plus one. In other words, what we've found is we've found the colon here in this example but then we're going to go one more, we're adding one to find our start location of a in this example. Otherwise, if we'd back all the way up to i equals one, then we're working with that scenario where there's no change in the e-mail address, and in that location, the start location is equal to one. Now in the first part of this multi alternative if then, if we found the start location then I'm just going to say Exit For so we don't have to waste our time searching for the start because we've already found it. So this is just kind of a time-saving step that we can do. So let's just make sure that this is working so far. I've got my spreadsheet over here and I'm going to put a breakpoint over here in the function and I'm going to type in equals Email of the cell next to it. I've dimmed the start location and it as integer. I'm also going to need end location as an integer. So now we go through and we've found our let's make sure we found our at location. So we look down here and the locals window, the length of the string is 46 and we're finding our at location at 34 which seems reasonable to me. And now we're going to go through. We're starting then at 33 and we're working backwards and we go until we find one of those things. And at some point then we're going to find the colon in this first example and that occurs here when i equals 21. So we bump into this part of the two way or the multi alternative if then statement and the start location then is equal to 21 plus one which is 22. So up here, the a in the Arnold is position 22, that's going to be our start location. Once we found that, we don't need to search anymore, so we bump out of the for loop. So now let's work on the end location. We're going to start at the at location, we're going to go plus one, and we are going to start it at the G. As soon as we find the greater than sign or the right square bracket, the end location is equal to i minus one. We back up one location. Or if we don't find any of those, then that means that our e-mail address is already in the format that we need and we've reached the end of the string and in that case the end location equals L. So we include a second for loop here where we're going from at location plus one to L. We're going character by character using the mid function to see if that equals the right square bracket or the greater than sign. If that's the case, we back up one and that's the end location. Once we find the end location, we exit the for. Otherwise if we don't find either the right square bracket or the greater than sign, then we've reached the end and in that case the end location is just the length of the string and we are done. Once we're done looking through that, we need to then output the result. The output of the function, the email function, is the name of the function. So email equals mid s, the start location, and then the length of the string is end location minus start location plus one and looks like we're good to go. I'm going to go ahead and put a breakpoint here. We know that the first part of this is working and I'm going to type in equals Email of the address next to it and press enter. And now we go through and we find the end location here. And in that case then we back up one and that's going to be our end location. We exit the for and the result is just the email address in cell C1 and this works on multiple scenarios. So I can drag this down and we can extract a whole slew of different email addresses and that's how we can do this. So this is a really good example of how you can create your own kind of a custom-designed function in VBA. And this is tremendously useful. Thanks for watching.
