Here is the beauty of Office 365 - the Excel team can push out updates between releases and the new version of Excel 2016 includes some killer functions - including TEXTJOIN that lets you concatenate a range of cells with a delimiter between each cell. Also covered here: the CONCAT function.
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1961 - TEXTJOIN & CONCAT in the Excel 365 February 2016 Release!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This is exciting, exciting news, if you have, not just Excel 2016, but if you have Excel 365, and you're in the First Release program, then you probably just got a new version of Excel downloaded yesterday, and there are some beautiful things in there.
Including a great new function called TEXTJOIN!
Let's do just a little bit of recap here: if you have first name in column A, last name in column B, I always join those together using the &, the concatenation operator.
But it is possible to use a function called =CONCATENATE, where you specify the first cell and the second cell, with a space in between, right?
We've had those forever.
The problem is, if you wanted to join, for whatever reason, several cells, right?
It would not do that, there is no good way to do that with the old function.
So, here's what they've given us, the first thing they gave us is a function called =CONCAT, so I can specify a whole big range, and it's going to take all those things and just jam them together, without any separators in between.
That is really, frankly, disappointing, that's not what we wanted.
Ah, but Joe and the rest of the people on the Excel team did the right thing!
Here is all of those values, check out this amazing formula!
So I'm going to do =TEXTJOIN, here's the delimiter ", " , do I ignore blanks, TRUE, yes, ignore empty cells, and then those are the cells we want to join!
Press Enter, and BAM, the whole thing is concatenated, with a comma and space in between!
What if it's not just a vector, what if it's a range like this?
It's going to do Andy Betty Charlie, from the first row, then Dave Ed Edward Eddy, from the second row.
Let's take a look at that formula.
Alright so, =TEXTJOIN, and here I said "Hey, don't ignore the empty cells!" So it actually included a space for that one.
Here, let's put a, so you can see it, a dash, " - ", and you'll see that it left the extra one in there.
What a beautiful, beautiful set of two new functions!
And there's more, go ahead and click the button up there on the top-right hand corner of this video, to see the next video about what else is new in Excel 365.
And hey, when you go to your IT department, say that you are looking for version 16.0.6528.1007 or higher, that's the version that includes these functions.
You might have to sign up for First Release, if you're watching this in February 2016.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
This is exciting, exciting news, if you have, not just Excel 2016, but if you have Excel 365, and you're in the First Release program, then you probably just got a new version of Excel downloaded yesterday, and there are some beautiful things in there.
Including a great new function called TEXTJOIN!
Let's do just a little bit of recap here: if you have first name in column A, last name in column B, I always join those together using the &, the concatenation operator.
But it is possible to use a function called =CONCATENATE, where you specify the first cell and the second cell, with a space in between, right?
We've had those forever.
The problem is, if you wanted to join, for whatever reason, several cells, right?
It would not do that, there is no good way to do that with the old function.
So, here's what they've given us, the first thing they gave us is a function called =CONCAT, so I can specify a whole big range, and it's going to take all those things and just jam them together, without any separators in between.
That is really, frankly, disappointing, that's not what we wanted.
Ah, but Joe and the rest of the people on the Excel team did the right thing!
Here is all of those values, check out this amazing formula!
So I'm going to do =TEXTJOIN, here's the delimiter ", " , do I ignore blanks, TRUE, yes, ignore empty cells, and then those are the cells we want to join!
Press Enter, and BAM, the whole thing is concatenated, with a comma and space in between!
What if it's not just a vector, what if it's a range like this?
It's going to do Andy Betty Charlie, from the first row, then Dave Ed Edward Eddy, from the second row.
Let's take a look at that formula.
Alright so, =TEXTJOIN, and here I said "Hey, don't ignore the empty cells!" So it actually included a space for that one.
Here, let's put a, so you can see it, a dash, " - ", and you'll see that it left the extra one in there.
What a beautiful, beautiful set of two new functions!
And there's more, go ahead and click the button up there on the top-right hand corner of this video, to see the next video about what else is new in Excel 365.
And hey, when you go to your IT department, say that you are looking for version 16.0.6528.1007 or higher, that's the version that includes these functions.
You might have to sign up for First Release, if you're watching this in February 2016.
Well hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
