I'm continuing on with a couple more examples of string functions, both in Excel and VBA. So we've got an example here where you have a bunch of part numbers. We wish to separate this into three component numbers, each in a separate column in Excel. I'm going to do this both in Excel and as a VBA array function in this screencast. So we're going to find the location of the first hyphen, and then we're going to find the location of the second hyphen. We're going to use those locations in our string functions in order to calculate the left-most number, so left of the first hyphen. We're going to use the "MID" function and the "RIGHT" function. In my VBA function that I'm going to create, I'm going to call this firstdash and the second location of that hyphen or the dash is going to be the seconddash variable. So before we get started with doing this in Excel, I wanted to just go straight to what we're going to be creating in VBA. I'm going to be creating an array function, which means I can highlight a multiple cells area, and then I can type in- I've named this the Parts function, and I reference my number there that has the three different parts. I do Ctrl, Shift, Enter, remember this is an array function, and it's going to separate that into the multiple parts. This could also be- I could have letters in there, too, and it's going to work on letters. So let's do this in Excel first, and then we're going to adapt this into a VBA array function. We're going to find the first dash location. So we can do that using the find function. So we're going to find a dash within this string, and that's going to give us the location of that first dash. Now the second dash is a little bit trickier. So remember that the find function will only find the first hyphen, but after we know that the first hyphen has been found, then we can use that as the optional third argument in the find function. So I'm going to use the find function. We're going to search for the same thing within the same text, but then this optional third argument is the start number. So I'm going to reference where we found the first one, but we want to start one greater than that. So when we do that, then we can easily find the second dash. Now we can use the "LEFT", "MID", and "RIGHT" functions to find the first, second, and third numbers. The first numbers are going to be the left, and remember it's not going to always be two numbers. It could be three. It could be four. Whatever. We want to make this so it's adaptable to all sorts of different numbers. So we want to do "LEFT" of that text, and we want to keep the two, so we're going to subtract one from the location, and that will give us those two numbers, the first set of numbers. That one's the easy part. The second, we can use the "MID" because this extracts text from within a string. So this is going to be the mid, the text is going to be our number, and we're going to start then at the first location plus one. Now the length of this is going to be the second dash location minus the first dash location plus one. So we're going to do the second dash location minus the first dash plus one. That should actually be a minus one. So now it's working. So we need a minus one instead of a plus one there. To get the right-most numbers, I'm going to use the "RIGHT" function with our text, and the number of characters, then, we're going to get from the length. So I'm going to do the length of our string so the length of our string minus the second dash location will give us nine. The length is nine, minus seven is two. So we're going to extract the right-most two characters of our string. In that case, we get the 23. So this is how we can use Excel to do this. And now this will work on a bunch of different numbers. So I could have mixed alphanumeric of different lengths, and I could copy these five columns down, and it works on any catalog number, whatever this is for, part numbers, so on. If I wanted to, instead of having these separate columns for B and C, wherever I use B3 and C3 in these formulas, I could just copy and paste the formulas that I had in here so we would have an embedded formulas inside these. So I could easily get rid of columns B and C. So now let's work on making a VBA array function to do this. So I've named this function Parts. I'm going to calculate a couple of things. I'm going to determine the length, which I'm going to refer to as big L. I'm going to find the firstdash position, the seconddash position. Those are all going to be integers. So let's do that first. So I've dimmed "L As Integer", "firstdash As Integer", "seconddash As Integer". Obviously, this Parts function has an argument, and I'm going to call that s, and it's going to be a string. The output of this function is going to be an array, so we need to dim the output as a variant, or we can leave that blank for the default data type. L is equal to the "Len", the length function of s. The firstdash position, we're going to use the "InStr". That's analogous to the FIND function in Excel. The seconddash position, we're going to use the "InStr" function again, but just like we did in Excel, where we started with one more than the firstdash position, we can do that with the "InStr" functions. We're starting with the firstdash plus one position, searching through our string for the second hyphen, or dash. Now what we're gonna do is I'm going to store the three different parts of this number in a vector, and I'm going to call that vector P. P is going to be size three, and we're going to dim that as a variant so it can accommodate numbers and letters. The first element of P is just going to equal now we're going to use the "Left" function. P(2), we can use the "Mid" function. So we use the "Mid" on our string. We start with the firstdash plus one. The length is going to be seconddash minus the firstdash minus one, similar to what we did in Excel. And finally, the third component of our output vector P is going to be the right-most digits. Now we are going to output this. The only thing we need to do is output. So to do that I just say the name of the function Parts equals our output vector, and this is by default a row vector. Keep in mind that whenever you dim an array or a vector in VBA, it's going to have a zeroth element, so I need to put in option base one here at the top. And now think I'm ready to go. So I'm going to go ahead and put a breakpoint in here. I'm going to go over to Excel. I'm going to highlight a one by three, and I'm going to type in my Parts function with my number. Ctrl, Shift, Enter and, we bump into the debugging here. So let's go through this. We calculate the length. Firstdash position should be three, which is three down here in the "locals" window. The seconddash position is seven, and that makes sense. We can open up P here. That's 67, and we get the mid, so that's 345. And we do this for the right, and we get 23. And finally, the output of the function is P. And then we end the function, and we look over here in Excel, and we have extracted those three different sections of that number separated by hyphens. I can then apply this to different part numbers, and I can drag this down. And we extract that information from the second part number. And maybe we had a big, long list here that we need to separate those components. So this is just another example, a very useful example how you can create a VBA function to do this. I showed you at the beginning how you can do this in Excel, but if you're doing this a lot, you may as well just put together an array function like this that you can apply to a lot of different scenarios. Thanks for watching.
