Module two is all about working with strings. So I'm first going to show you how to work with strings in Excel. There's a lot of sting functions. And then I'm going to show you how you can sort of do the same thing in DBA. There's advantages of doing these types of things in Excel. But also sometimes there's advantages of doing them in VVA, which I will try to highlight. So in Excel there's a bunch of functions that we can work with to manipulate strings. First, there's a LEFT function, I'm going to show you these through examples. There's the MID function. There's the RIGHT. So these take parts of the left, so the left parts of a string, the middle parts of a string and the right functions. Then there's the LEN, which determines the length of a string. And then we can find different strings inside of. So we can find substrings or just letters inside of a string. And there's also some lesser used string functions called the UPPER, LOWER, and the PROPER. So I'm going to show you these string functions in this screencast. I've got a word here, this is one of the longest words that's a single word in the English language. Pneumonoultramicroscopicsilicovolcanoconi- osis, it's something to do with inhaling silicone particles that are spewed out by volcanoes. But anyway, this is a really long word. I wanted to kind of show you some of these string functions that are already built into Excel. The first one is the LEN function, so that's the length. So you can just do the length of a string, and it'll just tell you how many characters there are. I should also note that if you have a space somewhere in here, it'll also count the spaces. It'll also count things like quotation and semicolons. So it doesn't just count the letters, it counts all the used characters, including spaces and punctuation. There's also the LEFT, RIGHT, and MID functions that are built into Excel. Let's say I only wanted to extract the first 7, so the left-most 7 characters of this word. Then you can type in LEFT of the string and then the number of the integer number of characters. And then it'll just extract that. We can also use the MID function, where you first argument is the string, but then you have start. So if I want to start with the 20th character, and then I wanted to output only 5, so this is the length. So I'm going to start with the 20th character, and I'm only going to output 5 of them. That will be copic, somewhere in there. We can also use the right function. We can reference our string. And then maybe I wanted the right most 5 characters. And that would be Iosis. So these are pretty useful in different scenarios. And I've got a couple examples that I'm going to show you in following screencasts. So there's a find function that's already built into Excel. So maybe I want to find micro within the text, then I can do that. And that will then tell me the character that might micro starts with in that. So the integer number, it starts at the 14th. Now you notice that when I type this in, so when I do find text, so maybe I wanted to find the string no, now there's two no's. There's a no here in the beginning, pneumono, but there is also a no in volcano over here. If you just do find("no" with a string, it'll find the first. It doesn't find multiple. So that's something you have to be aware of, it doesn't find multiple. However, if I click in here, there is a third optional argument, it's in brackets here, square brackets, so that I can start searching somewhere. So if I know that the second no is found after, let's say the 20th character, then I can start searching there. And that would then tell me that starting at 20, the next no is found overall at 36 of my string. Now there's a search function that's real similar to find. Search actually has a lot of different things that you can do. You can have wild card characters, you can use a question mark, an asterisk in them. I'm not going to show you the advance features of the search function, but just be aware there's some other things that you can do. Let's just say over here I put my name, and I do find, and I want to find a C within that text. Obviously, it's going to output 1, right? So that was a capital. The find function is case sensitive. So if I put a lower case c here, so let me just change this to a lowercase c, then it doesn't find any. And that's what you get when you don't get any matches. So the find function is case sensitive. If I use the search function, the search function actually is not case sensitive so I can apply that to my Charlie there, and it's going to find the 1, right? So it's not case sensitive. That's lowercase, and we find it, even though this is a capital letter. A couple other miscellaneous string functions in Excel. There's the upper, so the upper of a word just converts it all to uppercase. The lower of a word converts all characters to lower case regardless of how they are. There is a proper. So there's the proper function, which takes any lowercase word and just the first letter of that word, it just capitalizes it. Two more Excel functions I wanted to teach you guys. First there's the concatenate. So over here I've written Hi, my name is Charlie, in five different cells. So, first of all, there's the textjoin function. The textjoin will join text in different cells. So of a array over here. The first argument is a delimiter. So that's what's going to separate those words. So if I want to combine these five strings and these five cells into a single sentence separated by a space, the delimiter would be a space. So I put a space in there. The second argue is true or false, do we ignore empty cells or do we include empty cells? So if I had empty cells in here and I wanted to ignore those then I would put true. So it doesn't matter in this case because I don't have any empty cells but I'll just do true. And then here's where you can then highlight the range that has the strings that you want to combine. And when we do that we get, Hi, my name is Charlie. Now the last one is there's a concatenate function which joins strings. You can't highlight multiple cells like you can with the textjoin function, but you can do one and then the other. So I could do one, two, three. So the concatenate function by itself will just do that, but it slams everything right next to each other. If you wanted to do that, if you wanted to put spaces, then what you would have to do is after each one, you'd have to put another string in there. And then the result would be, Hi, my name is Charlie. So the textjoin is more useful when you have strings that you're trying to join in multiple cells. That's it for basic string functions in Excel. I'm going to follow this up with an example screencast in which we work with email addresses.
