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

Question

I'm compiling a database of business address info, including key staff for each business. The Google spreadsheet I've inherited for this project includes a column for 'key staff' (in column B), with multiple key staff names listed in the same cell, separated by line breaks (ie CHAR(10) ). There is one business per row. The number of lines in the 'key staff' cell varies row-by-row. My initial sheet looks like this:

I need to do the following to optimize this sheet:

1. split each multi-line ‘key staff’ cell so each key staff name appears on its own row. This requires that new row/s be inserted beneath the original row.
2. duplicate the data from all other cells on the original row (i.e. from columns A & C:E), so that each new row contains the full data for each business
3. I need an automated process - I'll have about 1000 businesses to process so can't do this with any manual steps

The sheet should then look like this:

Using =TRANSPOSE(SPLIT(B1,CHAR(10))) obviously only goes part of the way - it doesn’t insert new rows and doesn’t duplicate the surrounding column entries. All help gratefully accepted!

Answer 1

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).

Answer 2

A repeatable solution will need a script.

But for a one-time effort, you could just use =SPLIT(B3,CHAR(10)). This will give you all the person's names in side-by-side helper columns, like this:

Copy / Paste-special, values the helper-column contents.

And for each helper column that is used (hopefully not too many, 'cos hopefully you don't have too many people in any one business) manually copy-and-paste the block of rows to the end of the current block. (That's not a great description, but you get the drift.)

Answer 3

For folks who might not immediately grasp how to use the helpful custom function in the accepted answer:

• You need more than one sheet, in the example the two sheets are DATA and RESULT. The RESULT sheet is empty until the query has been run. You can see the query which refers to the DATA sheet in Jacob's screenshot.

• You will most likely need to modify the comparison value for k on line 8 which refers to the column where your data to parse should be found. The same number will need to go into the 2nd array value on line 4.

• You may need to modify the delimiter on line 4 which is currently \n

To make all this a teeny bit easier, I took the same code and extracted the delimiter and target column into variables set at the top of the function. As Jacob mentions target column count starts with 0 as the first number.

function result(range) {
  delimiter = ", "
  targetColumn = 10

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

References

• Custom Functions