Join several columns with a single formula returning an array

Question

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.

Answer 1

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 :

1. Change the name of the function from ARRAY_CONCATENATE to ARRAY_JOIN
2. The delimiter parameter was made optional.
3. Translation of error messages and JSDOC documentation.

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

=ARRAY_CONCATENATE("|",{A1:A3,B1:B3})

source

AA  Red
BB  Yellow
CC  Green

Will return

AA|Red
BB|Yellow
CC|Green

=ARRAY_CONCATENATE("|",A1:B3,B1:C3)

source

AA  Red     Dog
BB  Yellow  Cat
CC  Green   Rabbit

will return

AA|Red      Red|Dog
BB|Yellow   Yellow|Cat
CC|Green    Green|Rabbit

TO DO:

1. Improve error catching/descriptions
2. i18n( internationalization) I doubt that it's possible that custom functions include support for multiple languages, so maybe I should to move from gistGitHub to other code repository that allows to fork in order to have a version in English and other languages).

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

/**
 * Concatena los columnas de una matriz
 *
 * @param {"|"}   separador  Separador
 * @param {A1:B4} referencia Referencia o matriz de nXm
 * @return                   Matriz de nX1
 * @customfunction
 */
function ARRAY_CONCATENATE(separador,referencia){
  if(arguments.length < 2){
    throw new Error('Deben incluirse al menos dos parámetros.');
  }
  if(typeof(arguments[0]) != 'string'){
    throw new Error('El primer argumento debe ser una cadena de caracteres.');
  }
  for (var i = 1; i < arguments.length; i++) {
    if(!Array.isArray(arguments[i])){
      throw new Error('El segundo y siguientes argumentos deben ser rangos de dos dimensiones.');
    }
  }  
    var result = new Array;
    for (var i = 0; i < arguments.length-1; i++) {
      result[i] = new Array;
      for (var j = 0; j < arguments[i+1].length; j++) {
        result[i].push(arguments[i+1][j].join(separador));
      }
    }
    return transpose(result);
}

Helper function

/**
 * Tomado de http://stackoverflow.com/a/16705104/1595451
 * publicado en mayo 23 de 2013 a las 3:18
 * por [Mogsdad](http://stackoverflow.com/users/1677912/mogsdad)
 */
function transpose(a) {
  return Object.keys(a[0]).map(function (c) { 
    return a.map(function (r) { 
      return r[c]; 
    }); 
  });
}

Answer 2

This is what you are looking for:

Formula

=ARRAYFORMULA(TRANSPOSE(REGEXREPLACE(TRIM(SPLIT(CONCATENATE(IF(LEN(A:K),A:K&", ",)&REPT(CHAR(32)&"|",COLUMN(A1:K1)=COLUMN(K1))),"|")),",$","")))

Explained

The CONCATENATE formula builds a string (in a single cell) based on the cells that are not empty (IF and LEN) and adds a space (REPT and CHAR) and a delimiter (|). The SPLIT formula uses that same delimiter to split the results into a range (regarding the space as a result and thus using it in the range). The TRIM formula will remove the spaces. Finally, the REGEXREPLACE will remove the last comma. All is fed through an ARRAYFORMULA

Example

I've created an example file: Join several columns with a single formula returning an array