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

user_4385565 0
Viewed 1010

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.

google-sheets-query google-sheets-formula google-sheets
3 Answers

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

Example

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

user_38894230

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

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

enter image description here

E1:   =FLATTEN(A1:C3)
user_56326290

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))<>"")
user_43855650
Related