Google-sheets – Google Sheets split by “ ,” & add new rows

At first, I used plain old formula's to get the job done, see Output 2:

=JOIN(CHAR(10),SPLIT(Input!H3,";"))

Looking at the Output 2 sheet, it closely resembles the desired outcome. But, the outcome needs to have the empty lines in between !!

Then I revered to Google Apps Script.

Code

function myOutput3(range) {
  // create array 
  var output = [];

  // add header to array
  output.push(range[0]);

  // itterate through rows
  for(var i=1, iLen=range.length; i<iLen; i++) {

    // capture split results
    var spl7 = range[i][7].split(";"), spl6 = range[i][6].split(";"); 
    var len7 = spl7.length, len6 = spl6.length;
    if(len7 == 1 && len6 == 1) {
      output.push(range[i]);
    } else {
      // determine which column has the highest length
      var tLen;     
      if(len7 >= len6) {
        tLen = len7;
      } else {
        tLen = len6;
      }
      // prepare output array      
      for(var j=0; j<tLen; j++) {
        if(j == 0) {
          var arr = range[i];
          arr[arr.indexOf(range[i][7])] = spl7[j];
          arr[arr.indexOf(range[i][6])] = spl6[j];
          output.push(arr);          
        } else {
          output.push([,,,,,,spl6[j],spl7[j]]);
        }
      }        
    }
  }  
  return output;  
}

This custom function will do the trick, without any API calls.

Example

See example file I prepared: ESP classes row split example

Use a custom function.

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data

result

Example

I've created an example file for you: multi-line cells into new rows.

• Add the script under Tools > Script editor.
• Save the script, the name doesn't matter.
• Use the custom function in a cell just like a built in (see the screenshot above).