Parse out column with commas in it into separate rows

Question

I have data in the following 2 column format in my google spreadsheet:

3M            701
A.W. Sperry   DSA-2002
Accusplit     725MX, 705X, Magnum XL

I would like to be able to parse out the 2nd column by comma such that it looks like this:

3M            701
A.W. Sperry   DSA-2002
Accusplit     725MX
Accusplit     705X
Accusplit     Magnum XL

I'm a Java developer and can write a program to do this but was wondering if there is a way to do this right in google spreadsheets.

Answer 1

I think the easiest way to do this right in Google Spreadsheet is to write a program: a custom function. Once a custom function is entered in Script editor (under Tools) it becomes available within the spreadsheet: thus, calling =columnSplit(A1:B3, 2, ",") on your array will return the desired output.

The custom function is briefly documented using jsDoc, this will be shown if you start typing its name in a cell. The first argument is the range to split, the second is the number of column (1,2,...) which is to be split, the last one is the delimiter. The function trims the output cells to eliminate stray spaces around commas.

/** 
 * Splits the array by commas in the column with given index, by given delimiter
 * @param {A2:B20}  reference Range reference
 * @param {2}  index Column index
 * @param {","}  delimiter Character by which to split
 * @customfunction
 */
function columnSplit(reference, index, delimiter) {
  var input = reference;
  var output = [];
  if (input.constructor !== Array) {
    input = [[input]];                      // support for single-cell range
  }
  for (var i = 0; i < input.length; i++) {
    var parts = input[i][index - 1].toString().split(delimiter);
    for (var j = 0; j < parts.length; j++) {
      var copy = input[i].slice(0);         // slice(0) to get a fresh copy of the row
      copy[index - 1] = parts[j].trim();    // trimming is optional
      output.push(copy);
    }
  }
  return output
}