10

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.

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

4 Answers 4

Reset to default
8

The FLATTEN formula does exactly this.

=FLATTEN(A1:C2)
Improve this answer
1
  • Amazing - thank you so much!
    – NHDaly
    Commented May 17, 2021 at 22:46
5

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.

Improve this answer
4
  • I wonder if that worked ? :-|
    – JPV
    Commented Feb 28, 2015 at 20:57
  • It worked for me, the second one I mean.
    – Tapan Nallan
    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.
    – Tapan Nallan
    Commented Nov 12, 2016 at 12:36
  • 1
    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
0

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)

Example sheet: https://docs.google.com/spreadsheets/d/1O6xyJWojgYgEZpRHO1-12gw9c-MKTGvwfto7m2kCjpY/edit?usp=sharing

Improve this answer
0

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.