7

I want to combine two lists in Google Spreadsheet so that the cells interleave.
Both lists have the same amount of elements.

So that:

1          A
2          B
3          C

becomes:

1
A
2
B
3
C

The closest I came up with is something like JOIN({List A},{List B}), but in this way the Elements of List B are only split by the first Element of List A.

It would be great if someone has an idea without any add-ons using only the built-in functions. Transposing and formatting is not that important.

1 Answer 1

5

Try:

=ArrayFormula(transpose(split(concatenate(A1:B&char(9)),char(9))))

(note that concatenate has a limit of 50000 characters)

or

=transpose(split(join(" ",query(transpose(A1:B),,50000))," "))

Where list A starts in A1, list B in B1.

3
  • 1
    Both suggestions worked just great. Thank you very, very much JPV!
    – Liam
    Commented Jun 16, 2015 at 12:51
  • 1
    Or with the undocumented function FLATTEN as =FLATTEN(A1:B). In brief: Flatten flattens the selected array to 1D. The array is stored in row-major (C-style) order which means that it will naturally stack row-wise. Commented Dec 1, 2020 at 7:14
  • FLATTEN is the far superior function to use when dealing with string data for this scenario.
    – robyaw
    Commented Apr 28, 2021 at 15:40

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.