Looking for a way to populate a single column with all information contained within a range of cells. Let's say I want to take all of the data (A1:F12 , Sheet1) highlighted in the example picture and put it into a single column in the Sheet2 tab — is this possible?
2 Answers
- CONCATENATE everything with a chicken
SPLIT the chicken and TRANSPOSE
=ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(A1:F12&"🐥"),"🐥")))
-
1+1 for the chicken, but does not work with large data sets due to concatenate's 50,000 char limit Jun 8, 2018 at 9:02
-
You can also use anything else other than a chicken, in case anybody is confused with not having the emoji available. For example: =ArrayFormula(transpose(split(concatenate(A1:F12&char(9)),char(9))))– jloSep 18, 2019 at 11:14
So, I was looking for an answer to this and found this function that solve seamlessly this problem.
=FILTER(FLATTEN(range), FLATTEN(range)<>"")
With this you can solve your problem.
PS: I add this answer because with this you will not have the 50,000 char limit problem.