More of my discussion with Ann K. Emery. This episode talks about TEXTJOIN and how it would be nice to have TEXTSPLIT. Check out Ann's blog at Blog | Depict Data Studio
More Snippets here:
(0:00) Welcome and intro
(0:30) Discussion about TEXTJOIN and TEXTSPLIT
(1:01) Demo of TEXTJOIN using a range in Excel
(1:35) Generating random Yes/No using RAND in Excel
(1:54) Converting Excel formulas to values by dragging
(2:20) Using TEXTJOIN with an IF Function in Excel
(3:00) The need for TEXTSPLIT
(3:10) Counting names using LEN and SUBSTITUTE
(3:40) Converting Excel data to a paragraph in Word
(4:06) Concatenating text and numbers in Excel loses formatting
More Snippets here:
(0:00) Welcome and intro
(0:30) Discussion about TEXTJOIN and TEXTSPLIT
(1:01) Demo of TEXTJOIN using a range in Excel
(1:35) Generating random Yes/No using RAND in Excel
(1:54) Converting Excel formulas to values by dragging
(2:20) Using TEXTJOIN with an IF Function in Excel
(3:00) The need for TEXTSPLIT
(3:10) Counting names using LEN and SUBSTITUTE
(3:40) Converting Excel data to a paragraph in Word
(4:06) Concatenating text and numbers in Excel loses formatting
Transcript of the video:
Learn Excel from MrExcel Podcast episode 2407.
TEXTJOIN is great, but when are they going to give us TEXTSPLIT?
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today, more from my session with Ann K. Emery.
Check out her blog at https://depictdatastudio.com/blog/ . Let's get started.
We talked about some of your favorite features of Excel. What about any pet peeves or gripes?
I told you I have that blog post about some conditional formatting features I wish would just kind of go away.
They give us a function, two years ago called TEXTJOIN. TEXTJOIN is great.
It lets you do concatenation if I have a list of names in A2 to A20 and I want to put them in one cell with commas in between.
TEXTJOIN will let me do that and there's been talk that they need the opposite function. They need TEXTSPLIT.
You know I have a friend, an MVP who created a VBA SPLIT function that is one line of code.
It is literally one line of code. Why is this taking so long to get TEXTSPLIT?
I really wish we had TEXTSPLIT. So what I have over here.
I am going to put in Andy.
And I have a custom list already created here with a bunch of names like that.
And I want to put all those names into a single cell, right? So =TEXTJOIN(.
And I want to put maybe a comma space between everyone.
And ignore empty cells? Sure, I'll say ignore empty cells.
You can either put 1 or True there. One is always the same as True.
And then choose where your data is. Like that.
And what I get is Andy, Barb, Chris, Diane, Ed, Flo.
But now let's let's make it a little bit more powerful so we will do something like this.
So equal IF the RAND is less than .5, they are attending. So “Yes”.
Otherwise “No”.
What you get are a bunch of Yes and No. Alright, here's an awesome little trick.
I need to convert those formulas to values.
And while there are keyboard shortcuts for that, I learned this trick in Columbus Indiana. Go to the right edge of the data.
Here I right click and drag to the right. I don't let go of the mouse.
I'm still holding down the right mouse button and drag back and then when you started.
Let go and there is this awesome menu that pops up.
I have no idea how the guy ever discovered this.
Right-click, drag right, drag left, Copy here as Values Only.
Alright, so what I get there is now those Yes and No are permanent.
And I am going to say if that data in B2 to B15 is equal to Yes. Then, I want the corresponding cell from A2:A15.
Otherwise, I'm going to create an empty cell with quote quote.
Alright, and so what that's going to do is the third argument is going to evaluate everybody over there in column B. Get just the yeses and give me the names.
And so I get Andy, Barb, but not Chris and Diane, Ed Flo and so on.
And then I would change Chris from No to Yes you'll see that they show up there in that list.
So that's a great way to take data and put it like this.
But now, what I really want is I want TEXTSPLIT.
I want to be able to take that and break it out into a column or something like that.
And of course I can do with Text to Columns but I want a formula to do that. If I want to know how many names are there?
You have to count how many commas are there.
I would love to be able to just break that into an array and then do a COUNTA or something like that rather than have to do a length. Ugh it is just terrible. You have to do the LENgth.
And then take this commas out. And then do another length.
Compare the two.
Add one because there's no comma after the last person. It's just ugly.
So TEXTSPLIT would make a whole bunch of things easier to do.
Kirsten says wow, in all caps that is amazing. Yes, this is funny.
Sometimes I have a lot of data in Excel and I want to take that data to Word.
Not as a table but as a paragraph.
And TEXTJOIN will put that in a big paragraph for me and I just copy the whole thing out to Word and and take all my Excel data and now I can edit it in Word.
I do that for the transcripts for the videos.
That comes down in a file that ends up in Excel and I want it in Word and that's just a beautiful way to get it together.
Kirsten, you ask about concatenating text and numbers.
When you try to do that, it's super frustrating 'because the number always gets unformatted.
If you have a dollar signs in there with two decimal places.
And it happens to end in 50, you'll lose that last zero.
But you can concatenate the text cell A2 with the TEXT function.
And specify what format you want it to be so. Yes, that's right.
So it's a way to concatenate items without having to repeat the quote space quote.
Definitely.
That's it, and I think it came along in 2017, so there's a good chance that you have it. You know, unless you're back in 2010 or something like that.
TEXTJOIN is great, but when are they going to give us TEXTSPLIT?
Hey, welcome back to the MrExcel netcast. I am Bill Jelen.
Today, more from my session with Ann K. Emery.
Check out her blog at https://depictdatastudio.com/blog/ . Let's get started.
We talked about some of your favorite features of Excel. What about any pet peeves or gripes?
I told you I have that blog post about some conditional formatting features I wish would just kind of go away.
They give us a function, two years ago called TEXTJOIN. TEXTJOIN is great.
It lets you do concatenation if I have a list of names in A2 to A20 and I want to put them in one cell with commas in between.
TEXTJOIN will let me do that and there's been talk that they need the opposite function. They need TEXTSPLIT.
You know I have a friend, an MVP who created a VBA SPLIT function that is one line of code.
It is literally one line of code. Why is this taking so long to get TEXTSPLIT?
I really wish we had TEXTSPLIT. So what I have over here.
I am going to put in Andy.
And I have a custom list already created here with a bunch of names like that.
And I want to put all those names into a single cell, right? So =TEXTJOIN(.
And I want to put maybe a comma space between everyone.
And ignore empty cells? Sure, I'll say ignore empty cells.
You can either put 1 or True there. One is always the same as True.
And then choose where your data is. Like that.
And what I get is Andy, Barb, Chris, Diane, Ed, Flo.
But now let's let's make it a little bit more powerful so we will do something like this.
So equal IF the RAND is less than .5, they are attending. So “Yes”.
Otherwise “No”.
What you get are a bunch of Yes and No. Alright, here's an awesome little trick.
I need to convert those formulas to values.
And while there are keyboard shortcuts for that, I learned this trick in Columbus Indiana. Go to the right edge of the data.
Here I right click and drag to the right. I don't let go of the mouse.
I'm still holding down the right mouse button and drag back and then when you started.
Let go and there is this awesome menu that pops up.
I have no idea how the guy ever discovered this.
Right-click, drag right, drag left, Copy here as Values Only.
Alright, so what I get there is now those Yes and No are permanent.
And I am going to say if that data in B2 to B15 is equal to Yes. Then, I want the corresponding cell from A2:A15.
Otherwise, I'm going to create an empty cell with quote quote.
Alright, and so what that's going to do is the third argument is going to evaluate everybody over there in column B. Get just the yeses and give me the names.
And so I get Andy, Barb, but not Chris and Diane, Ed Flo and so on.
And then I would change Chris from No to Yes you'll see that they show up there in that list.
So that's a great way to take data and put it like this.
But now, what I really want is I want TEXTSPLIT.
I want to be able to take that and break it out into a column or something like that.
And of course I can do with Text to Columns but I want a formula to do that. If I want to know how many names are there?
You have to count how many commas are there.
I would love to be able to just break that into an array and then do a COUNTA or something like that rather than have to do a length. Ugh it is just terrible. You have to do the LENgth.
And then take this commas out. And then do another length.
Compare the two.
Add one because there's no comma after the last person. It's just ugly.
So TEXTSPLIT would make a whole bunch of things easier to do.
Kirsten says wow, in all caps that is amazing. Yes, this is funny.
Sometimes I have a lot of data in Excel and I want to take that data to Word.
Not as a table but as a paragraph.
And TEXTJOIN will put that in a big paragraph for me and I just copy the whole thing out to Word and and take all my Excel data and now I can edit it in Word.
I do that for the transcripts for the videos.
That comes down in a file that ends up in Excel and I want it in Word and that's just a beautiful way to get it together.
Kirsten, you ask about concatenating text and numbers.
When you try to do that, it's super frustrating 'because the number always gets unformatted.
If you have a dollar signs in there with two decimal places.
And it happens to end in 50, you'll lose that last zero.
But you can concatenate the text cell A2 with the TEXT function.
And specify what format you want it to be so. Yes, that's right.
So it's a way to concatenate items without having to repeat the quote space quote.
Definitely.
That's it, and I think it came along in 2017, so there's a good chance that you have it. You know, unless you're back in 2010 or something like that.
