0

Based on: Google sheets split multi-line cell into new rows (+ duplicate surrounding row entries)

How can I rewrite this for use of a comma as separator and remove blank spaces?

Input sheet:

Row 1: Albert A. Anderson, Beatrice B. Bargel ->  

The desired output on a new sheet:

Row 1: Albert A. Anderson  (plus surrounding cells)  
Row 2: Beatrice B. Bargel  (plus surrounding cells)  

Example spreadsheet

0

SCRIPT :

function transform(range) {
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][1].split(", ");    
    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 == 1) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

FORMULA :

=TRANSFORM(A1:E4)

RESULT :

4

  • Super cool. Thank you. This is exactly what I needed. You just made life easier for all my students :-) – Allan Bech Jan 10 at 8:13
  • Where in Your formular does it say "column b" if I ever want to change that? – Allan Bech Jan 10 at 13:05
  • ... and how does the formular know how many columns there is? If I ever decided to put extra columns in the sheets. – Allan Bech Jan 10 at 13:21
  • If You don't mind... Can You take another look at the example? I have tried to tweak your formular and it seems to work as intended except that it squeezes out a column (B). – Allan Bech Jan 11 at 8:26
  • @AllanBech squeezes? what does it mean? – MARK MY ANSWER Jan 11 at 8:44

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.