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

Improve this question

1 Answer 1

Reset to default
1

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

Improve this answer
7
  • Super cool. Thank you. This is exactly what I needed. You just made life easier for all my students :-)
    – Allan Bech
    Jan 10, 2019 at 8:13
  • Where in Your formular does it say "column b" if I ever want to change that?
    – Allan Bech
    Jan 10, 2019 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, 2019 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, 2019 at 8:26
  • @AllanBech squeezes? what does it mean?
    – user0
    Jan 11, 2019 at 8:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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