3

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?

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

    =ARRAYFORMULA(TRANSPOSE(SPLIT(CONCATENATE(A1:F12&"🐥"),"🐥")))
    
  • +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 '18 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 yesterday

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.