Now, for the second part of texts functions in Excel. So the previous screencast show you more or less how to split text into multiple parts. This screencast is going to show you how to join different texts or strings together. So the first one that I'm going to go over is known as the concatenate function. This is been around for many years. The concatenate function joins. Concatenate is just a fancy word for join. So we're going to join multiple strings or texts. The arguments must be single cells, which can make it awkward to use and it ignores empty cells. So let me show you an example of using the concatenate function. I'm working in this file called text functions and I'm going to compare concatenate, concat, and text join and just simply using the ampersand sign here. The concatenate function will join strings that are in different cells, but unfortunately, you cannot highlight the entire range like this, you just have to click on single cells. So it's a little bit annoying, but you can separate these by commas. What happens is it joins those into a single string. If I delete a cell here, the L may be the first L, you notice that it ignores blanks and just whatever's remaining gets squeezed together in a single string. So that's what the concatenate function does. The concat function is relatively new, starting in 2016. The argument can be a range of cells or multiple non-contiguous block of cells. It also, by default, ignores empty cells. So let's go ahead and use the concat function here. The concat function is nice because I can highlight a range of cells that has characters or strings in it that I want to join. So I can do concat there and press enter. It's ignoring blank cells. Let's go ahead and put this back to L. So it's a little bit easier to use than the concatenate because I can highlight an entire range of cells and not just click on the individual cells separated by commas that you have to do with the concatenate function. The concat function can accommodate multiple non-contiguous ranges. So maybe I wanted to combine range B2 to B6, concatenated with maybe a blank space, so I can put the blank space in there, and then maybe over here, I have there, the concatenate function again ignores blank spaces. So when I press enter, it's going to go ahead and concatenate this range over here with the blank that I put in the middle and then range I2 to I8 over here. Finally, the text join function was recently introduced with Excel 2019 and Office 365. It's very similar to the concat function, but you can determine the delimiters. That is, the character that you want to join different strings and also you can specify whether or not to ignore empty text. So let's type in an example for the text join function. The first argument is a delimiter. So this is where you can put whether or not those strings are going to be separated by a space or maybe you want to separate with a comma or whatever character, maybe a backslash or forward slash. So for now, I'm just going to put in a space and then we can put here an argument for or are we going to ignore empty cells or are we going to include empty cells. I'll just put in we're going to ignore that's probably more common. Then this is similar to the concat function in that we can put arrays of different cells that we want to combine. So I can do B2 to B6, and then maybe this region over here. Because I put true is going to ignore the empty text then I press enter. Now because I had that delimiter at the very beginning, which is a space in my example, it's going to put a space between each of those characters and sometimes that may or may not be desired. If I would've put false for this second argument, we have hello there, there's spaces between the H and the E, and the E and the R, because it is not ignoring the cells that are in I4 and I6, but it's putting spaces on either side. So it's actually two spaces between H and E, and E and R. Next I'm going to show you you can just simply use the ampersand sign and let me just make that a little bit bigger. So the ampersand sign, you can start equal and I can just type in hello, and then I can concatenate this. I've already showed a couple of examples of using this with numbers. So it doesn't have to be just text, it can be numbers. Maybe I had hello there, then it concatenates those two. The nice thing about the ampersand is you can do it in arrays. So I could do an array here. So I could do this array and the second array, which is a vector and this last one, and it's going to concatenate; Control, Shift, Enter if you got an older one, or just Enter if you've got newer versions and it's going to concatenate those FEE, FI, FO, FUM. So I just went over the ampersand. Two others are the trim functions and the exact function. The trim function remove spaces at the beginning or end of a string. It also ensures that there's only one space between two strings. The exact function outputs true or false depending upon if two strings are exact. The exact function is useful when you're trying to find perhaps exact name matches with two different sets of data. So let me show you an example of how we might use the trim function. Hello, my name is Charlie. I've got different number of spaces in between each of those words. Then trim function is just going to trim up that and it's going to remove multiple spaces and ensure that there's only one space between each of those text strings. If we put a space or two before hello, the trim function always remove spaces before the first string and after the last string in a string of characters. Finally, if we had two things like Charlie and Charlie, we can use the exact function. It's going to determine if two text strings are exact. These are very similar, but they're not exact because the bottom one is not capitalized and when we do that, they are exact matches. The exact function either provides a true or a false. I wanted to finish this screencast with a quick example. In the previous screencasts, I talked about how we can separate a last name, comma a first name into two different columns. So now what I'm going to do is show you a quick example where we can concatenate maybe those into an e-mail address, first name dot last name at some company.com. So I'm back into the names file where I left off. In the previous screencast, we had extracted using some text functions here, the last name and first name and now what I want to do is I want to concatenate these. We want to concatenate this into arnold.apple@somecompany.com. There's a couple ways to do this and you guys can experiment around with this, but I'm just going to simply use the concat function. So I'm going to concatenate and what I want to do is actually make these lowercase. So I'm going to do lower, I'm going to concatenate lower Arnold, that's first name comma with the dot, with a period, and then I'm going to do lower of apple and then I'm going to concatenate that with @somecompany.com. So that's how we can combine first name, last name, making them lowercase by using that lower function into a single e-mail address. Then now you would have those e-mail addresses that you might use internally inside your company. So hopefully, you learned a little bit more about advanced text functions, particularly, those that concatenate or join different strings in this screencast. Thanks for watching.
