An answer using native spreadsheet functions:
=ArrayFormula(TRANSPOSE(REGEXREPLACE(SPLIT(CONCATENATE(SUBSTITUTE(", "&B:B;", ";CHAR(10)&A:A&CHAR(9)));CHAR(10));"(.*?)(.*)";{"$1";"$2"})))
Disadvantage is that all columns are coerced to text strings with this method. There are workarounds for this, but the formula would get even more ridiculous.
An answer using a Google Apps Script custom function, which also trims leading and trailing spaces in the comma-separated list, as per the request in comments:
function advanceSplit(range1, range2)
{
var output = [], temp;
for (var i = 0, length = range1.length; i < length; i++)
{
if (range1[i][0])
{
var temp = range2[i][0].split(",");
for (var j = 0, length2 = temp.length; j < length2; j++)
{
output.push([range1[i][0], temp[j].trim()]);
}
}
}
return output;
}
and then invoke in a spreadsheet cell:
=advancedSplit(A:A;B:B)
My anecdotal observation is that for very large data sets, the latter method (custom function) has better performance than the former.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…