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
Commented Jan 11, 2015 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
Commented Jan 11, 2015 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
Commented Jan 11, 2015 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? Commented Jan 17, 2015 at 2:35
• I needed the result in multiple columns: webapps.stackexchange.com/a/172747/135478 Commented Nov 8, 2023 at 0:28

The `FLATTEN` formula does exactly this.

``````=FLATTEN(A1:C2)
``````
• Amazing - thank you so much! Commented May 17, 2021 at 22:46

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
Commented Feb 28, 2015 at 20:57
• It worked for me, the second one I mean. Commented Nov 12, 2016 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. Commented Nov 12, 2016 at 12:36
• You'll need to wrap the formula in an ArrayFormula. This should work =ArrayFormula(concatenate(A1:C&char(9)))
– JPV
Commented Nov 13, 2016 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)

You can also merge the cells. In your example, you'd need to transpose them first.