I'm using Google Spreadsheet and would like to convert a table into a single column of values so I can paste them into another program.

Original Data:

``````x y z
a b c
``````

Desired Outcome:

``````x
y
z
a
b
c
``````

Is there a formula I can use? I don't care what order the resulting list entries are in.

• So, I am assuming that selecting Column B, cutting its contents, and pasting it into the last cell of Column A, and then selecting column C and cutting the contents of it and pasting it into the last cell on Column A is not what you're looking for? – Rolo Jan 11 '15 at 5:18
• I would prefer not to as there are a lot of entries and the number of entries in each column is irregular. The entries are generated in the table and subject to change so something formulaic that will update automatically is better. – confuzzle Jan 11 '15 at 5:24
• I don't know if Google Spreadsheets supports macros, but I know what you want done can be done with a macro and Excel. Maybe someone here will provide you with the code. If not, look for "excel forum" on Google and you should see popular ones there were they are very helpful people. I once had someone write up a code for something simple and similar to what you wanted done. – Rolo Jan 11 '15 at 5:28
• What about generating an output to CSV, and then doing some string-replace-hackety-hack magic to get them in in a single column? – Josh Brody Jan 17 '15 at 2:35

It is not completely clear how your data is structured. In case cell A1 = x y z and cell A2 = a b c, and values are separated with " " , try:

``````=transpose(split(query(A1:A,,50000)," "))
``````

In case A1:C1 = x y z and A2:C2 = a b c, try:

``````=ArrayFormula(transpose(split(concatenate(A1:C&char(9)),char(9))))
``````

And in case both don't work, please consider sharing an example spreadsheet so we can have a better look.

• I wonder if that worked ? :-| – JPV Feb 28 '15 at 20:57
• It worked for me, the second one I mean. – Tapan Chandra Nov 12 '16 at 12:33
• I dont understand how the concatenation works though. If I just do concatenate(A1:C4&char(9)) , it throws an error saying an array value could not be found. – Tapan Chandra Nov 12 '16 at 12:36
• You'll need to wrap the formula in an ArrayFormula. This should work =ArrayFormula(concatenate(A1:C&char(9))) – JPV Nov 13 '16 at 16:51

If you repeat this formula it will generate a column of list data based on a table with these assumptions: One cell in the input is one cell in the output. Table width is 3 columns (adjust if needed in two places) List starts on row 15 (adjust if needed in two places)

=indirect("r"&floor((row()-15)/3)+1&"c"&mod(row()-15,3)+1,false)