7

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.

4
  • 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
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.

4
  • I wonder if that worked ? :-|
    – JPV
    Feb 28 '15 at 20:57
  • It worked for me, the second one I mean. 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. Nov 12 '16 at 12:36
  • 1
    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
4

The FLATTEN formula does exactly this.

=FLATTEN(A1:C2)
1
  • Amazing - thank you so much!
    – NHDaly
    May 17 at 22:46
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

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy