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]; 
    }); 
  });
}

For the OP example try the following formula:

=ArrayFormula(
IFERROR(
REGEXEXTRACT(","&
TRANSPOSE(TRIM(SPLIT(QUERY(TRANSPOSE(QUERY(
IFERROR(A4:A6&","&
REGEXEXTRACT(","&
SUBSTITUTE(B4:B6,", ",","),"^"&REPT(",+[^,]+",
COLUMN(OFFSET(A1,,,1,6))-1)&",+([^,]+)")&";")
,,1000))
,,1000),";"))),"^"&REPT(",+[^,]+",
COLUMN(OFFSET(A1,,,1,2))-1)&",+([^,]+)"
)
)
)

For the 30,000 rows:

• Change the references accordingly.
• Replace 1000 by 30000 or a larger number.
  • The 1000 is the headers argument of QUERY(data,query,[headers]). We want use a number big enough to include all the rows of the source data.
• If the second column of the source data has rows with more than 6 items, replace the 6 in OFFSET(A1,,,1,6) by the corresponding number of items.

See the reference for further details.

References

JOINing code-points after CHAR conversion in Google Spreadsheets