12

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:

enter image description here

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:

enter image description here

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!

12

First of all, sorry for the late answer, but I have a solution for you to work with.

Code

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

Explained

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
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.
Add the script under Tools>Script editor and press the save button.

  • 2
    Awesome awesome awesome - buy this man a beer! Many thanks Jacob, that's exactly what I need. – kirk tab Jun 11 '14 at 22:49
  • 1
    And how to define the range? ( get this message "cannot read property "length" from undefined" Thanks! – user2060451 Jan 14 '17 at 12:16
  • on a really large file, this will likely fail...result too large. – Kyle Pennell Apr 30 at 0:57
3

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:

enter image description here

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

  • Hi Mary, thanks but I need the individ. person names dropped into new ROWS, (with extra rows auto-generated to accommodate them), then the surrounding info copied beside the new 'person' cells. And I need a 100% automated process - I can't do this manually for a thousand-or-so businesses! – kirk tab May 23 '14 at 8:22
  • You don't need to do the manual step for the whole thousand businesses. You only need to do it for the maximum number of pepole associated with any one of those businesses: eg if one business has six people, then there will be six new helper columns created, so you have to to a manual copy-and-paste six times, one for each column. – MaryC.fromNZ May 23 '14 at 10:08
  • 1
    Someone could write a script for this (I'm not that someone!) - but really the time spent testing it would probably be greater than the time spend doing it manually. – MaryC.fromNZ May 23 '14 at 10:09
2

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

  • 1
    If I run the code, it says: TypeError: Cannot read property "length" from undefined. thank you so much. Answer should be here: Answer must be somewhere here (developers.google.com/apps-script/reference/spreadsheet/…) But I keep having the same error. – user2060451 Jan 14 '17 at 12:18
  • @user2060451 The solution on this answer shows the code of custom function, it's not intended to be ran from the script editor. I added a link to the custom function guide from Google. – Rubén Oct 16 at 16:11
2

Single formula

=ARRAYFORMULA(substitute(IFERROR(
  REGEXEXTRACT(
    "_"&transpose(split(join(" ",query(substitute(
      IFERROR(transpose(FILTER(A:A,LEN(A:A)))&
      "_"&REGEXEXTRACT(CHAR(10)&transpose(FILTER(B:B,LEN(A:A))),
        "^"&REPT(CHAR(10)&"[^"&CHAR(10)&"]*",row(OFFSET(A1,,,6,1))-1)&CHAR(10)&"([^"&CHAR(10)&"]*)")&
      "_"&transpose(FILTER(C:C,LEN(A:A)))&
      "_"&transpose(FIlTER(D:D,LEN(A:A)))&
      "_"&transpose(filter(E:E,LEN(A:A))),),
      " ","|"),,1000))," ")),
    "^"&REPT("_[^_]*",COLUMN(OFFSET(A2,,,1,4))-1)&"_([^_]*)"
   )
),"|"," "))

Based on an answer by Markus Jarderot to trying to split within arrayformula inside google spreadsheet

How it works

Part 1. The solution in the linked answer

The question was about how to split a column of values separated by dots by using an array formula.

The formula proposed was the following:

=ARRAYFORMULA(IFERROR(REGEXEXTRACT("."&A2:A;
    "^"&REPT("\.[^.]*";COLUMN(OFFSET(A2;;;1;4))-1)&"\.([^.]*)")))
  • OFFSET(A2;;;1;4) returns a 1 x 4 array, the values doesn't matter.
  • COLUMN returns {1,2,3,4}
  • REPT returns ={"","\.[^.]*","\.[^.]*"\.[^.]*","\.[^.]*"\.[^.]*","\.[^.]*"}
  • "."&A2:A concatenates a dot to each cell value in A2:A. The result is an array.
  • REGEXEXTRACT extracts the matching sub-string for each cell on the previous array, returns #N/A to the corresponding array element if there isn't a match.
  • IFERROR replaces the errors by blank cells.
  • ARRAYFORMULA expands the result to required area by the resulting array.

Part two, adaptation

In the formula on the "Single formula" section, it was used two times.

Split the values in column B

Formula

REGEXEXTRACT(
CHAR(10)&transpose(FILTER(B:B,LEN(A:A))),
"^"&REPT(CHAR(10)&"[^"&CHAR(10)&"]*",row(OFFSET(A1,,,6,1))-1)&CHAR(10)&"([^"&CHAR(10)&"]*)"
)&

In the regular expression, the separator \. was replaced by CHAR(10) as it is the one used by the OP. Also, COLUMN(OFFSET(A1,,,1,6) was replaced by row(OFFSET(A1,,,6,1)) in order of to split the values in rows instead of columns. This was done to be able fill rows.

Fill the rows

To fill the rows, first the transpose of each other columns were concatenated to the result of the previous formula, in the following way:

transpose(FILTER(A:A,LEN(A:A)))&"_"&

The formula of the previous section

&
"_"&transpose(FILTER(C:C,LEN(A:A)))&
"_"&transpose(FILTER(D:D,LEN(A:A)))&
"_"&transpose(FILTER(E:E,LEN(A:A)))

"_" was included to separate this strings later

Concatenate the values

As QUERY concatenate the rows using a space as separator, first the original spaces were replaced by | , then we used QUERY without the second argument an large number as a third argument. As Google Sheets has limits both on small a big numbers, 2E6 was used as it's the maximum number of rows that a 1 column spreadsheet could have.

Join and split the transposed columns

The transposed columns first were joined using an space as separator, then the result was split by .

Getting back the original shape

Transpose again to get the original shape, and use pattern described on Part 1 to split the strings by _.

Final touch

Replace |by spaces.

protected by Community Sep 12 '16 at 12:51

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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