Combine Data with CONCAT and TEXTJOIN

Quick reference

• 00:03 Combining data in Excel is an integral part of the data cleaning process.
• 00:10 Quite often, particularly if you do a lot of importing
• 00:13 of datasets from external systems, those datasets won't come across perfectly.
• 00:17 We might have things like full addresses in one cell that we need
• 00:21 to break up across different columns.
• 00:23 And that is where we would need to know some simple, yet
• 00:27 effective Excel functions for combining data together.
• 00:30 And we're going to look at three of them in this lesson.
• 00:33 So let's dive into our first example.
• 00:36 So what I have here are some first names in column B,
• 00:39 some middle names if they have them in column C, and some last names in column D.
• 00:45 And what my boss has asked me to do is basically
• 00:48 combine all three of these into one cell.
• 00:51 So we don't want them split across different columns.
• 00:53 Now the first method that you can use to do this is simply use
• 00:57 the ampersand symbol.
• 00:59 Now what does the ampersand symbol do in Excel?
• 01:02 Well, it tells Excel that you want to join one piece of text to another
• 01:07 piece of text.
• 01:08 So if we click in cell F8, let's use the ampersand to join the first name together.
• 01:14 So we're going to type in equals and all we need to do is select the first
• 01:19 cell B8 and then we use the ampersand symbol to join it to the next cell.
• 01:24 Now, bear with me here.
• 01:26 I'm going to select the second cell.
• 01:28 We're going to do ampersand and then we're going to select the third cell.
• 01:32 So I'm just joining all three of those cells together.
• 01:35 Now, what do we get when we press enter?
• 01:38 Well, we kind of get what we want, but we actually want spaces in between each of
• 01:42 the names, the first, the middle, and the last.
• 01:46 So what you need to remember when you're using this method is that the space
• 01:50 is also considered a piece of text and we need to join it together.
• 01:54 So we want the first name then we want to join it with a space and
• 02:00 that needs to go inside quote marks.
• 02:03 We need to add another ampersand, and then we need to go and
• 02:09 add another space and add another ampersand.
• 02:13 Let's hit Enter.
• 02:14 That looks so much better.
• 02:16 Now what happens if I copy this formula down?
• 02:19 Well, it looks like it works pretty well for all of these names.
• 02:23 But however, take a closer look.
• 02:26 Some of these people, for example, Sandy Hunter and
• 02:30 Susie Conway, they don't have a middle name.
• 02:34 So how has the formula dealt with that?
• 02:36 Well, if we take a look at Sandy Hunter,
• 02:38 you can see that there is actually a double space in the middle there to
• 02:42 account for the fact that she doesn't have a middle name.
• 02:45 Now, this is a very small but important little thing to note because effectively
• 02:50 what we now have is inconsistent spacing throughout our dataset.
• 02:54 So we want to make sure that we're trimming off any erroneous spaces,
• 02:58 any spaces that shouldn't actually be there.
• 03:01 So to do that we can add the TRIM function into our formula.
• 03:05 And if you've never used the TRIM function before, it is such a useful feature for
• 03:10 just getting rid of spaces that don't need to be there.
• 03:12 So I'm going to edit my formula up in the formula bar and
• 03:16 all we're going to do is we're going to add TRIM on the front there.
• 03:20 Now there's only one argument for TRIM, that is text.
• 03:23 The text is going to be generated by the formula that we already have, so
• 03:27 we just need to close off the TRIM function.
• 03:30 Let's hit Enter, and this time when we copy down, keep your eye on Sandy Hunter
• 03:35 because you should notice that it moves in because it's removed that erroneous space.
• 03:40 So that is the first method that you can use to combine text together,
• 03:45 ampersand symbol.
• 03:46 Let's take a look at another method.
• 03:48 Now, we do actually have a formula or
• 03:51 a function in Excel that combines text together.
• 03:54 And that function is called CONCAT.
• 03:56 You might previously know this function as CONCATENATE.
• 03:59 That's the old name that was changed in the last few years to CONCAT.
• 04:04 So what we can do here is we can use CONCAT.
• 04:07 And notice as soon as I start to type it in,
• 04:10 we still have the old CONCATENATE function just there.
• 04:13 So this is here as a legacy function.
• 04:16 So if you are using a much older version of Excel where you don't have CONCAT,
• 04:20 you're still going to have access to CONCATENATE.
• 04:22 And the good news is that both of these functions work in exactly the same way.
• 04:26 So I'm going to choose CONCAT, and this is slightly different to the ampersands,
• 04:31 we just need to provide the text.
• 04:33 So I'm going to select the cell, comma.
• 04:36 Now remember, we still need to add in the spaces as a piece of text.
• 04:40 So text two is going to be our space in quote marks.
• 04:45 We then want to join it with the middle name, then we want a space,
• 04:49 and then finally the last name.
• 04:50 Let's close the bracket, hit Enter, and
• 04:54 then when we double click to copy this down we get the same result.
• 04:58 Now we have the same issue here with Sandy Hunter and Suzie Conway.
• 05:03 So we need to go back up and do our TRIM trick.
• 05:05 So let's do this in the formula bar.
• 05:07 Let's just add TRIM on the front, close it off.
• 05:11 And then when we copy this down,
• 05:13 we should find that those erroneous spaces are removed.
• 05:17 Now, another function in Excel that you can use to join text together is TEXTJOIN.
• 05:22 And this is a much newer function than something like CONCAT.
• 05:26 And what TEXTJOIN allows us to do is basically take a data set and
• 05:30 join it together using a delimiter that we've specified.
• 05:33 And we can do other things with text join such as ignore any blank cells.
• 05:38 So let's take a look at a simple example of how TEXTJOIN works.
• 05:43 So on this worksheet, I just have a list of names.
• 05:46 And maybe I want to join all of these names together in one cell, and
• 05:51 I want them separated with a comma space.
• 05:54 So we can use TEXTJOIN to do this.
• 05:59 Now, notice we have a few arguments here.
• 06:02 The first argument is the delimiter that we want to use.
• 06:05 So what do we want separating each name?
• 06:08 Now for me, I want that to be a comma space.
• 06:12 And remember that needs to go in quote marks.
• 06:15 We can then specify if we want to ignore any empty cells.
• 06:20 So I'm going to say TRUE here even though we don't have any empty cells and
• 06:24 then we need to provide the text that we want to join.
• 06:28 And this is really the advantage of TEXTJOIN over something like CONCAT
• 06:32 because we can select a range.
• 06:34 So I can simply select the range of cells, close the bracket, hit Enter and
• 06:38 I get exactly what I'm looking for.
• 06:40 So TEXTJOIN allows you to select a range to join together, whereas CONCAT,
• 06:44 we have to select each cell individually.
• 06:47 Now, remember in this formula we selected TRUE for ignore empties.
• 06:51 So let me just show you what that means.
• 06:53 If I was to go in and delete Ben out of this list, you can see the formula updates
• 06:58 and no one would be any of the wiser that Ben was actually in there.
• 07:02 Whereas if I was to change this to FALSE, where we're not ignoring empties,
• 07:06 you can see that we get this double comma in there instead.
• 07:10 So that is the difference between the TRUE and the FALSE argument in this formula.
• 07:16 Now, before we leave this lesson,
• 07:18 I just want to show you a more advanced use of TEXTJOIN.
• 07:21 So in this final example, we have a list of names and
• 07:24 we have the projects that have been assigned to each of these employees.
• 07:30 And what I want to be able to do is I want to be able to select the project name over
• 07:34 here and I wanted to show all of the members all of the people who are working
• 07:38 on this project in one cell underneath.
• 07:41 So what we can do here is we can say,
• 07:43 TEXTJOIN, What delimited do we want to use?
• 07:48 Well, I'm going to use a comma space again.
• 07:50 Do I want to ignore empties?
• 07:51 Well, say yes, TRUE.
• 07:53 And then we can combine TEXTJOIN with an if statement.
• 07:58 So I'm going to go straight into if and we're going to say
• 08:03 if the project is equal to what we have in cell N6.
• 08:07 If that's true,
• 08:08 then we want to show the names of the people assigned to that project.
• 08:13 If it's false, we want nothing in there.
• 08:16 So let's close the bracket and we need to close the TEXTJOIN as well.
• 08:20 Hit Enter and check out what we get.
• 08:22 So now I should have only the members of Project Beta listed in here.
• 08:27 Now, you could put this on the data validation drop down.
• 08:30 I haven't for the moment so let's just manually change this to Project Alpha.
• 08:35 Hit enter and everything updates nicely.
• 08:39 So those are the three methods that I generally tend to use when I
• 08:43 want to combine data together in one cell.

Lesson notes are only available for subscribers.