5

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?

Improve this question

2 Answers 2

Reset to default
7
  • CONCATENATE everything with a chicken

  • SPLIT the chicken and TRANSPOSE

    =ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(A1:F12&"🐥"),"🐥")))
Improve this answer
2
  • 1
    +1 for the chicken, but does not work with large data sets due to concatenate's 50,000 char limit
    – András Szepesházi
    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
5

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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