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

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

  • 1
    +1 for the chicken, but does not work with large data sets due to concatenate's 50,000 char limit Commented 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
    Commented Sep 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.

  • Thanks @jóni for the clean approach suggested. Worked like a charm. Commented Dec 19, 2023 at 3:12

Your Answer

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

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