How to concatenate rows in excel?

October 22, 2008 2:38 PM

I have a bunch of rows in Excel. What's an easy way to string them all together into one row, in the order in which the rows appear (i.e. later rows' cells come later in my one-long-row)? I do not want to put all the cells into one cell, I want to keep all the cells separate, just in one row. Thanks!
posted by shivohum to Computers & Internet (10 answers total)

Select your items in your column, copy, then right-click at the destination, paste special, and tick the Transpose box. What you want is exactly what Transpose is for. Here's a little more detail on the Microsoft site.
posted by pocams at 2:51 PM on October 22, 2008

Best answer: I'd export to a CSV file, open in a text editor or Word, replace ^p with a comma, then save and reimport into Excel. Then everything should be on one row with your cells maintained.
posted by gatorbiddy at 2:52 PM on October 22, 2008

pocams is right, transpose is your money.
posted by milqman at 3:00 PM on October 22, 2008

Transpose will work for a single column -> a single row, but it won't turn multiple columns into a single row the way the OP described.
posted by gatorbiddy at 3:02 PM on October 22, 2008

I think gatorbiddy is right and that's exactly how I would do it.
posted by b33j at 4:07 PM on October 22, 2008

It's true that you can't transpose multiple columns in a single go, but you can certainly transpose each column into the new row. Just keep working your way right to the next empty cell in your destination row for a paste starting point.
posted by willnot at 4:32 PM on October 22, 2008

willnot
To "transpose" a large number of columns into rows (say, a 10x10 grid of data) would take a very long time - almost as long as cut-and-pasting the data by hand.

Furthermore, shivohum didn't say (s)he wanted to transpose the data, columns for rows. If the order is important, this method won't work at all.

Cut&paste into Word, replace all ^p with ^t, and cut&paste back into Excel, as others have said, is how I always do it.
posted by IAmBroom at 4:44 PM on October 22, 2008

Best answer: If you don't want to fiddle with the export/replace method (which should work), the following VBA will export to a tilde-delimited file of one row. Change the delimiter as you need, of course. You'd need to define the length and width of your table in the first couple of lines, or you could just change "Range(Cells(1, 1), Cells(x, y))" to "Cells" to do the whole worksheet... but that will take a while.


------
Sub thing()

x = 20 ' define length
y = 20 ' define width

For Each c In Range(Cells(1, 1), Cells(x, y))
a = a & c.Value & "~" ' change this tilde for, say, CSV
Next c

Open "C:\outfile.txt" For Output As 1
Print 1; a
Close 1

End Sub
-------
posted by pompomtom at 8:28 PM on October 22, 2008

Excel has a limit of 256 columns. So your one long row can't be that long without requiring export to another type of file.
posted by oceanmorning at 9:03 PM on October 22, 2008

Response by poster: Wonderful, thanks everyone. Exporting to a tilde-delimited file might make the most sense given the 256-column limit.
posted by shivohum at 9:42 PM on October 22, 2008