Combine data using CONCATENATE, CONCAT, and TEXTJOIN functions and the ampersand (&) character

- [Instructor] Excel has a number of different techniques for pulling data together from different locations. Not uncommon to see these locations next to one another in adjacent cells. Often, the technique will involve using a function, perhaps one called CONCATENATE. If you use Office 365, and you have installed the latest updates, you also have the capability of using a new function called CONCAT, ultimately this will replace CONCATENATE, and another function called TEXTJOIN. Another feature, another capability that's been around for awhile is simply using the ampersand symbol.

So let's show these different techniques here. So, we've got some entries over in column A, we've got a whole list of products, they've all been identified by their item numbers, we're changing our identification system. We're now going to call it an ID number, and we're going to pull together that item number along with the codes we've been using for color and size and location to create an ID something like the one we're seeing here. You can see that's a combination of the data to the left. So, how can we do this, 'cause we've got lots of these. We don't want to do it manually.

One way is we could use CONCATENATE. Now, if you go to the Formulas tab in the ribbon and choose Text, if you have Office 365 and have installed the latest updates, you don't see CONCATENATE here even though it's still available. Here's that new function called CONCAT, and you escape from here. So, I'm going to type = and I think many of you know as you start to type a function name, often you will see it in the list below. I typed co.

Why do I not see CONCATENATE? Good question, don't have a good answer, as soon as I type the n, I will see it. There it is and I can use it. And it's been around for a long time in Excel. So what do we want to do here? Pull in the data from A2 and a comma and B2 and a comma and so on. Looks like I put in two commas there, not necessary. And a comma after the C2 and D2, all done, enter. And the same thing we got over here manually, so it's looking like that. If we want to display this, I'll use a function off to the right.

You probably don't use this a whole lot. I use it for documentation purposes. It's called FORMULATEXT. There it is right there. We'll double-click to tap it into place. This simply allows us to display the actual formula that might exist in another cell. I'll do it right there. So that's what the function looks like as we're using it in cell F2. Now, we could've used a different technique using the ampersand symbol, the character found on the number seven key, and of course it means and as we use it in text and that's the way that we'll use it here.

So, instead of the commas here, I'm going to put this in. Eventually, I'll just take out the word CONCATENATE, too, so we could be using this technique. Shorter, but perhaps a bit garbled looking. So, we'll take out the parentheses and all this data to the left. If you didn't know what that symbol meant, you'd have to figure it out based on the context here, but this will give us, essentially, the same kind of answer. This time, we're working off the data in row three. And once again, I'll use FORMULATEXT off to the right, I'll simply copy this downward, to remind us what's being seen here.

Now, how does this new function work, =CONCAT? Remember, you won't have this unless you have Office 365. Left parenthesis, simply highlight these cells. There we are. That's a lot easier, here, too. As a reminder, I'll copy down FORMULATEXT to see how that works. That allows us to highlight the contiguous cells. It's much more efficient, less typing, works more easily, and we don't have to type in CONCATENATE, although we could've double-clicked it. Anyway, that's a new technique that's going to be handy at times.

Now, what if we wanted our new ID number to have dashes in it between these various components? You don't want to watch me type, so what I'm going to do is drag the CONCATENATE function as I used it in cell F2 using the control key I'll drag this down here, go with the mouse first, and make a change here, but this time after each comma, I'm going to type "-" and a comma, and also over here, "-" and a comma, and finally after the C5 comma, "-".

Big opportunity for typing mistakes in here, too. So, we could use that technique. That works reasonably well. Let's try that with the ampersand symbol. Again, I don't want to do too much typing here, but we could make that slightly easier, but here, too, if you were typing what I'm about to show you now, it would take some time, you're likely to be making some typing mistakes. So in each of these positions where you have a comma, we'll put in the ampersand symbol. And that should work, but I made a small typing error in here. I'll go with the suggestion. Looks like we got it right.

And as a reminder, up to the right, I'll drag that FORMULATEXT function downward, so we can remind ourselves what's being used here, and make this a bit narrower, and that a bit wider so we can see this. So we're seeing different techniques here for pulling this data together. Now, this last technique could work even better with a new function called TEXTJOIN. What's the delimiter we're going to be using here? It's going to be the dash. Double quote, dash, double quote, comma. Are there any empty cells in here? If there are, we want to think out, do we want to put anything in its place or not? We're just going to ignore them here.

In other words, we would put in nothing, but we don't have any anyway in the example so we just put in a comma. And now, where's the text we want to join? It's those cells off to the left there. So that's going to be a lot easier and faster, and we get the result done there even better. So, one by one, each of these techniques, I don't know that everybody needs them all the time, but I think you can see, when you are trying to combine data from different locations, we've got a wealth of features available here, mostly by way of functions and the ampersand symbol, and once again, the CONCAT and TEXTJOIN functions will not be available unless you're using Office 365 and you've installed the latest updates.