4

translating data range into single column

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 2

7
  • CONCATENATE everything with a chicken
  • SPLIT the chicken and TRANSPOSE

    =ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(A1:F12&"🐥"),"🐥")))
    
2
  • 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))))
    – jlo
    Sep 18, 2019 at 11:14
3

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.