In a previous screencast, I talked about the stream functions in Excel and now I want to go through the equivalent string functions in VBA. So, we had the left function in Excel, in VBA there's the same, there's the left function that matches the left, there's also the mid and the right and the Len. So let's do the same thing that the Excel functions did. There is no find function but there's something known as the InStr or InString and I'll show you how this works here and an example. Let's look at these in VBA. So I've just created a sub here, typed in S as a String and here we have our big word S, and now I'm going to show you how we can use the left function. So here we can message box the leftmost seven digits of our string. The Mid function will start with the tenth, so the second argument where you start, and the third argument is how long. So when we run this, the message box the 10 letters starting with item 10 of the string. And finally the right function, the right most five, we can use using the right function and we get iosis. We can use the Len function to calculate the length of our String and then we can use the InStr. It's very similar to that find or search, the arguments are a little different order, so we can have the start by default. You start at one, so you can leave that off. But then, you look through the string for different substrings. So I can do where is micro found? And when we run that, it tells us that we have micro starting at the fourteenth character of our string. And similarly, if you wanted to find multiple matches, if I knew that the second NO was after the first one, we can search through our string to find the first match after, so starting at letter 20 or character 20. And when we go through that, we find that it starts at the thirty sixth position. We've got the UCase, so uppercase, we'll make it all uppercase. We've got the lowercase, so that's with an L. So similar to the lower function in Excel, that makes everything lowercase, and there is no equivalent to the proper function in Excel. In Excel, we have the CONCATENATE and TEXTOIN functions. In VBA, we have the ampersand and you guys have already got a little bit of experience with this in the first part of the course. By using message boxes and input boxes you can concatenate numbers and strings using the ampersand sign, so that's one way to do that. There's also a join function that's built into VBA. It doesn't join strings like the ampersand does but it joins strings that are found in an array. There's also something known as the Split function, splits a string using a delimiter. This is sort of the opposite of the text joint function in Excel. We can split a longer sentence into smaller words using a delimiter, like a space, or a comma, or semicolon, and so on. So I've got a couple examples that I wanted to show you that we will work through so you can understand the join and the split functions in VBA. So I've made a Sub routine called JoiningStrings, with Dim B as a vector size three, and B is just composed of A, B, and C. And then I'm going to use the join function to combine these into a single word. So I'm combining elements of a vector into a word. And when I go through this then, we join that and we get A, space, B space C. What you can't see from here is there's actually a space before the A. The reason for that is that when we do this B actually has a zeroth element. And you should have learned from the previous module, in order to eliminate this, we want to add in Option Base one. Now when we run through this, we don't have a zeroth element of B and there isn't a space before the A, which could be problematic in some scenarios. Now you probably want to remove the spaces. So I'm going to show you how we can just quickly implement a For loop to remove these spaces. First, instead of using the message box here, I'm going to set the joint message, equal to join, and I have to Dim that variable. So Dim joined message as String, I'm also going to have just a message as String, that's going to be our final message. Now we're going to go through a For loop to look through the odd. We're going to extract the odd elements of the joined message so that the A position one and then three which should be B, and C which is position five. So we're eliminating the spaces at two and four. I've implemented a For next loop here where we go for I_ equals_ one to the length of the joined message, which will be five in this case, in increments of two. So we're going to go one, I_ equals_ one, three and five. And we're just going to extract them. We're going to ignore the spaces that occur at even numbers of I. And during each iteration, we're just setting the new message. The message will start out as blank, it's going to equal the old message, plus we're going to use the Mid function and we're going to start with the i element of the joined message. We're going to do one character at a time. So when I_ equals_one, we're just going to take the A of length one, then I will be bumped up by two because of that step two to three. Then the new message will be A_ plus_ the third element, and in that way, we're going to then add just the single letters in the odd positions. We need to Dim I as an integer. So we run through this, we create our B vector that I can open up here. And then we create our joined message which is A space, B space, C. Now we're going to iterate just through the odd numbers of I and we're going to save the message down here and locals window we'll start out as A, now I is three because we bumped it up by two. So the message here is AB and then we have C. So that's how you can take spaces out of this. However, what if we didn't have just single letters, we had maybe multiple strings of varying size? Then we'd have to look for spaces that are not in a regular pattern. To do this, I can modify my For next loop. First I'm going to remove the step two because we don't have a consistent step, we're just going to go by one. And I've added an If Then statement. If not made joined message. So if I start with the I_ equals_one. So, If the first element or the second element, you know these are all length one, so we basically sort through all the letters one at a time. If they're not equal to empty, so if they're not, actually this should be a space. If they're not equal to a space, then the message will be the old message starting from nothing plus one at a time, we're going to take the I letter or character of the joined message, and we're going to add that to our message. Forgot to put an If there. So now when we run through this, we create our B vector. You see B then when after we join it into the message we have spaces here between the B and the C and the E and the F. So we go through this. The first letter is not a space. So A is not a space, B is not a space, but then C is a space. So we skipped right over that. When we don't have a space, we combine those letters one at a time into our message string. So we bypass that now, I is equal to four, so that C is not a space, so we added that to our message, and that's how we can go through and eliminate all the spaces when we don't have a consistent format.
