Mike from Kansas City asks if there is a way to take words from many adjacent cells and merge them into a single cell. Episode 578 shows many approaches to this problem.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Well come back to the MrExcel netcast.
I'm Bill Jelen.
Today, we have a question sending by Mike in Kansas City.
If you have a question for the podcast, you can call and leave your question as a voicemail or just drop me an email bill@mrexcel.com and we'll get to you.
Like I said that he has several rows of data and with words going across the columns that he needs to join into a single column.
There's not always the same number of words in each case and he says you know is there's some fast way to do this.
Well, you know we always use the the CONCATENATE formula.
So, =A1&" "&B1.
And so on down the line.
But, that can get really tedious.
Especially, If you have, 7 or 12 or 15 words.
So, that's not the best way to do it.
Now, I told Mike that this way is really not going to be satisfying.
If you select all of those words control+C, go to a new blank spot, Edit, Paste Special and choose Transpose then Click OK.
Now, you can select all of those words, plus a whole bunch of extra columns and use Edit, Fill and then choose Justify and Excel will put all the words together.
But, I said I can't imagine that is going to be the most fun thing in the world.
The one other option that I thought of was to use View, and then Task Pane and display the Clipboard.
Now, the clipboard is interesting because when the clipboard is shown and you copy several things.
So, I'll copy the first one then the next one, then the next one, the next one, than the next one, then the next one, then the next one, I Can go to a new cell, type an apostrophe and click Paste All and it will paste all of the items on the clipboard.
Unfortunately, it paste them with carriage returns or line feeds in between, I'd have to go back and turn off wrap text and even then I'm going to have this frustrating unprintable characters.
So, I have to use the CLEAN function, to get rid of that, not going to work.
So, I think the best thing that I can come up with is to select all of the cells at once.
Use control+C and then switch over to a blank notepad window, use control+V to paste, control+A to select all the cells, control+C to highlight everything again.
And then go back to Excel type your apostrophe and control+V not the fastest way certainly and actually I want to invite you.
If you a faster way to merge all these cells from A1 through G1 together into a single cell that's better than the notepad way.
Please, drop me a note bill@ MrExcel.com and we'll get that on a future podcast.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Today, we have a question sending by Mike in Kansas City.
If you have a question for the podcast, you can call and leave your question as a voicemail or just drop me an email bill@mrexcel.com and we'll get to you.
Like I said that he has several rows of data and with words going across the columns that he needs to join into a single column.
There's not always the same number of words in each case and he says you know is there's some fast way to do this.
Well, you know we always use the the CONCATENATE formula.
So, =A1&" "&B1.
And so on down the line.
But, that can get really tedious.
Especially, If you have, 7 or 12 or 15 words.
So, that's not the best way to do it.
Now, I told Mike that this way is really not going to be satisfying.
If you select all of those words control+C, go to a new blank spot, Edit, Paste Special and choose Transpose then Click OK.
Now, you can select all of those words, plus a whole bunch of extra columns and use Edit, Fill and then choose Justify and Excel will put all the words together.
But, I said I can't imagine that is going to be the most fun thing in the world.
The one other option that I thought of was to use View, and then Task Pane and display the Clipboard.
Now, the clipboard is interesting because when the clipboard is shown and you copy several things.
So, I'll copy the first one then the next one, then the next one, the next one, than the next one, then the next one, then the next one, I Can go to a new cell, type an apostrophe and click Paste All and it will paste all of the items on the clipboard.
Unfortunately, it paste them with carriage returns or line feeds in between, I'd have to go back and turn off wrap text and even then I'm going to have this frustrating unprintable characters.
So, I have to use the CLEAN function, to get rid of that, not going to work.
So, I think the best thing that I can come up with is to select all of the cells at once.
Use control+C and then switch over to a blank notepad window, use control+V to paste, control+A to select all the cells, control+C to highlight everything again.
And then go back to Excel type your apostrophe and control+V not the fastest way certainly and actually I want to invite you.
If you a faster way to merge all these cells from A1 through G1 together into a single cell that's better than the notepad way.
Please, drop me a note bill@ MrExcel.com and we'll get that on a future podcast.
Hey, thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
