Putting the few rows into one Excel 2007 column instead

Question

Is there any way of putting the few rows I have in an Excel 2007 spreadsheet into one column instead?

a b c 
d e f 
g h i

to

a 
b 
c 
d 
e 
f 
g 
h 
i

This is just a sample try out. I am working with large data set of 2000 values and want to know how I can convert the rows into a single column.

I can think of a rather involved way of doing it in excel (using indirect and offset) hoping someone else has a better way. How many rows are there?
@soandos If it is a text file, then you can use a programming language to iterate over each token (value of interest) and place them on their own line.
@iglvzx Right, but if it starts out as a non csv excel file, its harder
@cody_q, if you are using Excel as in your other question, which version of Excel are you using?

soandos · Answer 1 · 2012-06-15 06:15:05Z

Making some assumptions here: It is excel, and the number of rows is small.

There are two steps here:

Combine the rows into a single row (just put row 2 after the end of row 1, etc).
Select all of row 2, and copy it. Right click in an unoccupied area, and select transpose (or paste special->transpose). Move it where-ever you want.

Note: the copy and paste areas for the transpose cannot overlap.

Bernhard · Answer 2 · 2012-06-15 08:10:13Z

up vote 1 down vote

If you're using VI as text editor, you can easily do

:%s/ /\r/g

If you are willing to use sed, you can from the commandline in Linux type

sed 's/ /\n/g' filename

edited Jun 15 '12 at 8:10

answered Jun 15 '12 at 7:37

Bernhard
729210

2 Answers