Google Spreadsheets: Formula to "interleave" or "zipper" multiple arrays

Question

Tried searching and checking Google documentation but I am still having issues getting the results that I want for this problem.

I'm needing to split several comma-separated lists of items and recombine them so that first items in each list is combined, followed by the second item, etc.

To ask with an illustration, if I have a cell with the following

Paul, John, George, Ringo

and another cell with the following

McCartney, Lennon, Harrison, Starr

How do I use one function to produce this in a cell?

Paul McCartney, John Lennon, George Harrison, Ringo Starr

Answer 1

Here you go, you will need to copy to every row - but assuming your data is in columns A and B it will work across a dynamic number of items:

=ARRAYFORMULA(join(",",split(A1,",")&" "&split(B1,",")))

See image example below, in row one i used the data you showed up top, and in row two i reversed the column data just to show as an example:

Answer 2

If you paste the first word CSV to Sheet1 and the second word CSV to Sheet2 then you could put the following into sheet3

In Cell A1:

=Sheet1!A1&" "&Sheet2!A1

This is saying take the value in sheet1 A1 and then a space " " then the value in sheet2 A1

You could also do this with the concatenate function:

=CONCATENATE(Sheet1!A1," ",Sheet2!A1)

Then just copy the formula to suit.