How to concatenate rows in excel?
October 22, 2008 2:38 PM Subscribe
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!
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
posted by gatorbiddy at 2:52 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
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
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
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
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
------
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
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
posted by shivohum at 9:42 PM on October 22, 2008
« Older To duct tape or not to duct tape... | Can I have both a visible tattoo and a teaching... Newer »
This thread is closed to new comments.
posted by pocams at 2:51 PM on October 22, 2008