Today, in Episode #1375, Why doesn't the =CONCATENATE function accept a range? Why can't you specify a separator character? It is not that hard. Today's episode shows a simple macro using a defined function that would solve the problem.
Transcript of the video:
Learn excel from MrExcel Podcast. Episode 1375.
Better Concatenate Hey, welcome back to the learn excel podcast. I'm Bill Jelen from MrExcel. I had a big problem last month I was working on this great DVD you know Mike Gervin put out the slaying excel dragons book and he put together a DVD of 53 videos that go along with the book almost 16 hours of video content.
And I was building that in InDesign and in this box here I needed to list all of the topics in that video separated by these bullet characters, 53 of these.
Now, what Mike had.
He had a list and there he is using word, a list in word of all of those topics.
I needed to take those topics from word and produce it in IndDesign so ofcourse my natural inclination is Excel.
I mean, we can solve everything with excel so the data comes over here from word to excel I had to do a little bit of Data, Text to Columns Fixed-width to get rid of the numbers.
All right great. Everything's good. They're all right. You know, but then my problem here is Excel has the concatenation character the ampersand and they have a concatenate function But it's just ugly so =D15& You know that's going to show up with the two things jammed together if I want to put that bullet character in between that form is going to get really really long, and I'm always just really frustrated that the concatenate function doesn't just accept a range.
I mean why do I have to go through and specify cell by cell by cell by cell by cell it just makes no sense to me and this is something that's just been bugging me for a long time and so finally for this one because I had 53 of them to do I said That's it I've had enough let's switch over to VBA and just solve this and it was not that difficult at all. I creat it.
So in VBA Alt F11 to get to VBA Insert Module and then I type Function Join, and I asked for two things.
First variable is called RangeToJoin That's a range and then something to put BetweenEach so that way I could put a space between them or in this case I needed a pay space bullet space All right and you start out here anytime you have a function that function name has to be a variable that's used in the function.
So I started out with join = nothing and I say for each cell in the range join is equal to join and the cell value and whatever text I passed is between each eithrt a space or bullet or in this case the space full of space, and then next cell.
So it just goes through gets a hold of those cells, you know but then the problem is I have a bullet at the very end.
So if they passes anything if the length of the join at the end is greater than zero if there was something there then take the left Basically the length of join minus whatever between each was so in my case space bullet space and we're done all right now.
Next problem. I had was the bullet. Let's see right here, so I copied a little bit from word where I had the bullet. I need to figure out what that character that is so I was able to isolate that using a function here called MID and I just kind of eyeball at mid of that cell ,8 ,1 Sure enough that got me the bullet and then to figure out what that code is That code I use the =CODE(N3) and that tells me that the bullet is character Character 149 all right beautiful right.
Now let's go back down here to our final result.
So I used my new user-defined function =JOIN of this whole range here , then what do I want to put in between each one in quotes a space and then &CHAR that's the character function (149) another & and another space and check that out takes that vertical list of individual cells and produces a really really nice very very long one bit of text that has all the time separated by bullets.
Now ofcourse from here copy go back to indesign and paste crazy going from word to indesign and having to stop in excel.
But that was the best way I could figure of solving this problem very very quickly.
and hey by the way Mike's DVD is out now So if you are more of a I'd rather watch someone show me how to do it then read This is a great great product 53 different videos here ranging anywhere from a few minutes up to half an hour for some of the really complex ones.
So pivot tables VLOOKUPs IF statements the entire gamut of everything you would need in excel.
So just a great video check it out at MrExCel.com Well hey, I want to thank everyone for stopping by. We'll see you next time for another netcast from MrExcel.
Better Concatenate Hey, welcome back to the learn excel podcast. I'm Bill Jelen from MrExcel. I had a big problem last month I was working on this great DVD you know Mike Gervin put out the slaying excel dragons book and he put together a DVD of 53 videos that go along with the book almost 16 hours of video content.
And I was building that in InDesign and in this box here I needed to list all of the topics in that video separated by these bullet characters, 53 of these.
Now, what Mike had.
He had a list and there he is using word, a list in word of all of those topics.
I needed to take those topics from word and produce it in IndDesign so ofcourse my natural inclination is Excel.
I mean, we can solve everything with excel so the data comes over here from word to excel I had to do a little bit of Data, Text to Columns Fixed-width to get rid of the numbers.
All right great. Everything's good. They're all right. You know, but then my problem here is Excel has the concatenation character the ampersand and they have a concatenate function But it's just ugly so =D15& You know that's going to show up with the two things jammed together if I want to put that bullet character in between that form is going to get really really long, and I'm always just really frustrated that the concatenate function doesn't just accept a range.
I mean why do I have to go through and specify cell by cell by cell by cell by cell it just makes no sense to me and this is something that's just been bugging me for a long time and so finally for this one because I had 53 of them to do I said That's it I've had enough let's switch over to VBA and just solve this and it was not that difficult at all. I creat it.
So in VBA Alt F11 to get to VBA Insert Module and then I type Function Join, and I asked for two things.
First variable is called RangeToJoin That's a range and then something to put BetweenEach so that way I could put a space between them or in this case I needed a pay space bullet space All right and you start out here anytime you have a function that function name has to be a variable that's used in the function.
So I started out with join = nothing and I say for each cell in the range join is equal to join and the cell value and whatever text I passed is between each eithrt a space or bullet or in this case the space full of space, and then next cell.
So it just goes through gets a hold of those cells, you know but then the problem is I have a bullet at the very end.
So if they passes anything if the length of the join at the end is greater than zero if there was something there then take the left Basically the length of join minus whatever between each was so in my case space bullet space and we're done all right now.
Next problem. I had was the bullet. Let's see right here, so I copied a little bit from word where I had the bullet. I need to figure out what that character that is so I was able to isolate that using a function here called MID and I just kind of eyeball at mid of that cell ,8 ,1 Sure enough that got me the bullet and then to figure out what that code is That code I use the =CODE(N3) and that tells me that the bullet is character Character 149 all right beautiful right.
Now let's go back down here to our final result.
So I used my new user-defined function =JOIN of this whole range here , then what do I want to put in between each one in quotes a space and then &CHAR that's the character function (149) another & and another space and check that out takes that vertical list of individual cells and produces a really really nice very very long one bit of text that has all the time separated by bullets.
Now ofcourse from here copy go back to indesign and paste crazy going from word to indesign and having to stop in excel.
But that was the best way I could figure of solving this problem very very quickly.
and hey by the way Mike's DVD is out now So if you are more of a I'd rather watch someone show me how to do it then read This is a great great product 53 different videos here ranging anywhere from a few minutes up to half an hour for some of the really complex ones.
So pivot tables VLOOKUPs IF statements the entire gamut of everything you would need in excel.
So just a great video check it out at MrExCel.com Well hey, I want to thank everyone for stopping by. We'll see you next time for another netcast from MrExcel.
