Is there a Google Sheets function to squeeze a range into one column?

Question

I wanted to find the built-in function for this to no avail so I had to write it in script:

function squeeze(range) {
  return [].concat(...range).filter(n => n)
}

This JS function flattens the 2D array range and returns it.

Note that it filters out empty cells with uneven columns.

So multiple columns into a single column? There should be questions like that on SO — JvdV, Aug 06 '20 at 09:09

JPV · Accepted Answer · 2021-01-08T07:22:34.060

5

While not officially documented (yet), flatten() also exists as a built-in formula. See here for more info.

UPDATE: Since the end of 2020 the function is now documented. See here for more info.

edited Jan 08 '21 at 07:22

answered Aug 06 '20 at 09:13

JPV

Cool but not quite exactly the same: it leaves empty cells if the columns are uneven. I've tried using FILTER with ISBLANK but it says the range needs to be a row or a column? `=FILTER(FLATTEN(B2:L7); ISBLANK(B2:L7))` – Dlean Jeans Aug 06 '20 at 15:19
1

For anyone who needs to debug `FILTER range must be a single row or a single column.`, the second argument (the condition) must be flattened also. So it's `=FILTER(FLATTEN(B2:L7); ISBLANK(FLATTEN(B2:L7)))` – Dlean Jeans Aug 07 '20 at 06:18

score 5 · Answer 2 · answered Aug 06 '20 at 09:23

5

if FLATTEN gets removed by some evil google dude you can do:

=TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(A1:C3),,9^9)),,9^9), " "))

E1:   =FLATTEN(A1:C3)

answered Aug 06 '20 at 09:23

player0

score 0 · Answer 3 · answered Aug 07 '20 at 06:15

0

Thanks to JPV and based on his answer, I added FILTER to leave out empty cells:

=FILTER(FLATTEN(B2:L7), FLATTEN(B2:L7)<>"")

Plus TRANSPOSE, I didn't specify this but I wanted multiple columns into one, not multiple rows into 1 column.

=FILTER(FLATTEN(TRANSPOSE(B2:L7)), FLATTEN(TRANSPOSE(B2:L7))<>"")

answered Aug 07 '20 at 06:15

Dlean Jeans

You can also use `QUERY` to filter out empty cells: https://webapps.stackexchange.com/a/158280/279532 – Gairfowl Aug 07 '21 at 08:14

3 Answers3