This is the opposite of my question Split a column of strings with a single formula returning an array. I have several columns, say, A-K, which I want to join into one comma-and-space separated column L. Since more data may be added to the sheet (by a script, a user or a form), I need a solution that works with an array.
The best I have so far is
=arrayformula(A1:A&", "&B1:B&", "&C1:C&", "&D1:D&", "&E1:E&", "&F1:F&", "&G1:G&", "&I1:I&", "&J1:J&", "&K1:K)
which is a pain to type. Is there a better way?
There is a join
function, but it works only in one dimension: both =join(", ", A:K)
and
=arrayformula(join(", ", A:K))
throw errors.
Best Answer
Short answer
I have the same situation. I did my homework (research) but didn't found anything, so I build a custom function. Next are the links:
Demo spreadsheet
Public gist
NOTES: The custom function have been changed. The majors changes were :
The public gist includes the last changes at this time. The demo spreadsheet include both version of the custom function.
TODO: Update the code below this section.
Explanation
The custom function in this answer is part of a large personal project. It's purpose is join the columns of one or several sets by sets. Accepts one or multiple ranges but they should have the same number of rows.
ARRAY_CONCATENATE(separador,referencia1[,referencia2, referencia3])
separador: is a string to be used as separator
referencia1 [,referencia2, referencia3]: One or more references or arrays.
The custom function requires a helper function. Both are included in the gist as separate files and here as two code blocks.
Example of use
source
Will return
source
will return
TO DO:
NOTES: At this time the comments are in Spanish because I was thinking to post the different parts of the project first at Stack Overflow in Spanish which scope includes the topics of several programming sites in the SE network.
Custom Function
Helper function