I need help with what I guess would be close to the opposite of importing a list separated by commas (or whatever) and placing each entry into a separate column. I want to take the info from several rows and place them into one cell, with the entire list separated by commas. Makes sense? Is there any way to do that? I tried Googling then quit because I found more than one person asking this, yet no solution to my problema. Using Excel 2010. Halp?
¡Gracias!
On the data tab, there’s a ‘Text to Column’ button. You can select the data and remove all of the breakpoints from it and it will merge everything in a column into a single cell. You’ll have to do it column by column. There’s probably a more efficient way of doing it, though; There always is with Excel.
If you know Visual, you could probably write a macro to do it. Just store all the column in an array and output each element into a single cell.
Look up the “concatenate” and “concolumnate” functions. They will take multiple columns and turn their contents into one cell in the order you specify. I do not remember the specific differences between the two, but I’m sure the function wizard will walk you through the use and syntax.
I’m second the concatenate recommendation. Concatenate each cell, separated by a comma.
Thirding this. Assuming you are concatenating three columns A, B and C, you want a formula along these lines in D1:
=CONCATENATE(A1, ",",B1, ",", C1)
Who is CONCOLUMNATE? My version of Excel (2010) does not recognise this.
This thread’s the top Google result for “concolumnate”. All the other hits are in Spanish for “con columna te”.
Excellent! Concantenate works! But it keeps jamming the text together, and the only way I can figure out how to separate it is to write out the formula. The formula works, but it’s not any faster than pasting, which is what I was trying to avoid. Anyone? Thanks again!
Concolumnate probably means the same as ‘transpose’, I think.
Another thought - it’s a bit of a work-around, but it should work:
Save your file as a .csv. Then go to the folder where you saved the file and rename it with a .txt extension. It’ll say, “If you change a file extension, it may become unstable,” but just blow through that.
Then right-click on the .txt file you just created and use the Open With to open it back up in Excel … and you should see everything in one column with commas. You can then re-save that file as Excel and you should be good to go.
What do you mean the text is jammed together? The code Dervorin posted should return: A1,B1,C1. If you want it to be A1, A2, A3 you just need to add a space after the comma in quotes. So:
=CONCATENATE(A1,", ",B1,", ", C1)
Once you do it for cell D1, you should be able to just drag it down all the way from D1 to D(however many rows you’ve got). Then you just have to delete columns A-C and you should be done.
Important note: my work around only works on files with one worksheet, as neither .csv nor .txt supports multiple worksheets. Also, the entire file will be affected, so if it’s just a section of data on your file you need to manipulate, yank it out and work with it on it’s own.
You do know that once you have the formula in one cell, copy it down to the others…?
You don’t have to do it one at a time.
I assume you know this.
Another hint.
Once you have done something like the CONCATENATE trick, select the column, copy, then use right click - PASTE SPECIAL - values and paste over top of it (or another column)
This replaces the formulas (formulae) with the actual values shown in the cells. Now you have the actual values, not a formula.
Oops, forgot that step! You need to do that before you delete the other columns.
Right, when I write out the formula in the foruma bar everything works easy peasy, appropriate spacing, punctuation and all, but I’m hoping for a faster way than to write it out. There’s a concatenate formula tool on the toolbar, but when I use that, I can’t figure out how to separate the text the way I’d like to.
Yes, but when I do that, I get an error.
Here’s exactly how I have it written in my spreadsheet. It’s actually separated by a semicolon and not a comma, but whatever:
=CONCATENATE(B1,"; ",B2)
Works like a charm! Then I drag it down and get an error message. The new formula with the error reads as:
=CONCATENATE(B1:B9,"; ",B2)
Tinkering around with other responses now…
Sounds like maybe you’re dragging while you’re still entering the formula. After you enter the formula in D1, push enter to accept it, select D1 and grab the black square in the lower right corner of the cell (the cursor will turn into a small black plus sign). You should then be able to drag it down however far you want and the cells in the “D” column should fill with the same formula, but with the numbers reflecting the row. In other words D2’s formula becomes A2, B2, C2 instead of A1, B1, C1, and so on.
I was doing that. I don’t know why.
Okay, now doing it the seems-like-right-way-but-isn’t. Formula in B1:
=CONCATENATE(B2,"; ",B3)
Works great. So now the text in B1 reads “B2; B3” just as I want it. Grabbed the lower right corner of B1, dragged it through all the rows I want, and now I get rows of semicolons?
The formula in B2 says:
=CONCATENATE(B3,"; ",B4)
And it goes on like that.
I want all of the text in B1 to read: “B2; B3; B4; B5” etc. I feel like this should be easy but I’m making it hard. 
Try select row of cells with correct formula in top box, then, edit----> fill ----> down
or ctrl+D does it too I think
If you need to maintain column or row integrity on something use $A$1 instead of A1 it will always use that same cell in the formula even if other cell references change.
You should really try my work-around. If it’s just some simple columns it will work.
I work with data files that have to be set up as comma-delimited text files, and we convert things like this to and from Excel on almost a daily basis.
I’m confused. Which of those two things is correct? (bolding mine). Both “B2; B3” and “B2; B3; B4; B5” can’t be right. I suspect there’s something more complex that you’re trying to do than merely concatenating across columns, so if you could share some example data and tell us how you want it to look in the end, that might make it easier.
For example, if you start with these three columns:
A B C
Dave Male 20
Barry Male 40
Ann Female 30
All the results so far are going to end up with something like this:
A B C
Dave; Male; 20
Barry; Male; 40
Ann; Female; 30
Is that the sort of concatenation you’re aiming for?