Google-sheets – How to keep all the values when I split a cell by delimiter (comma) in Google Sheets

formulasgoogle sheetsgoogle-apps-script

I have a spreadsheet in Google Sheets, where I am currently running a script.

There are two tabs, the first tab is the following:enter image description here

The second tab is the following: enter image description here

This is the script I am running: 

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

In the second tab on the spreadsheet, in cell A1 I have the following function:

=result(Data!A1:AA)

What I am trying to get out of the script is that the names listed in Columns G-I (in the first tab) split where there is a comma and all the surrounding data is kept the same.

As you can see in the second tab it only splits the names in column G, but column H & I it only keeps the first attendee.

Is there a way to modify the script to get it to split all attendees in columns G-I?

Best Answer

Query-based

Again, there are many ways to answer your question. Please consider this as one option for a solution.

This answer is NOT perfect but I am including it because it opens up the scope to look at options for displaying your data.

Props to @player0 for the answer to Comma separated list into matched columns pairings and infoinspired Split to Column and Categorize – Google Sheets Formula which gave the clue about adding further columns into the query.

I wrote three queries:

Restaurant Attendees =Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(G1:G),REGEXREPLACE(G1:G&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&H1:H&"|"&I1:I&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col1,Col8,Col9",0)

Restaurant query

School Attendees =Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(H1:H),REGEXREPLACE(H1:H&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&G1:G&"|"&I1:I&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8, Col1, Col9 ")

School Attendees

Park Attendees =Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(I1:I),REGEXREPLACE(I1:I&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&G1:G&"|"&H1:H&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8,Col9,Col1")

Parks

Combined

I combined the queries by wrapping them in curly brackets}

={Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(I1:I),REGEXREPLACE(I1:I&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&G1:G&"|"&H1:H&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8,Col9,Col1");Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(G1:G),REGEXREPLACE(G1:G&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&H1:H&"|"&I1:I&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col1,Col8,Col9",0);Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(H1:H),REGEXREPLACE(H1:H&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&G1:G&"|"&I1:I&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8, Col1, Col9 ",0)}

Combined three queries

You'll notice that the column headers are repeated. I do know how to remove column headers, but nothing that I tried in this case worked, so I left them in place as something for you to research/troubleshoot. ;-)

Edit 8 Sept 2020

This revision includes the "edit Url" column, and also takes out the duplicate Column Headers.

={Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(G1:G),REGEXREPLACE(G1:G&",",",","|"&TEXT(A1:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B1:B,"dd/mm/yyyy")&"|"&C1:C&"|"&D1:D&"|"&E1:E&"|"&F1:F&"|"&H1:H&"|"&I1:I&"| "&"| "&"| "&J1:J&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col1,Col8,Col9,Col10 label Col10 'edit Url'",1);Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(H2:H),REGEXREPLACE(H2:H&",",",","|"&TEXT(A2:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B2:B,"dd/mm/yyyy")&"|"&C2:C&"|"&D2:D&"|"&E2:E&"|"&F2:F&"|"&G2:G&"|"&I2:I&"| "&"| "&"| "&J2:J&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8, Col1, Col9,Col10 ");Query(ArrayFormula(TRIM(split(transpose(split(textjoin("|",1,if(len(I2:I),REGEXREPLACE(I2:I&",",",","|"&TEXT(A2:A,"dd/mm/yyyy hh:mm:ss")&"|"&TEXT(B2:B,"dd/mm/yyyy")&"|"&C2:C&"|"&D2:D&"|"&E2:E&"|"&F2:F&"|"&G2:G&"|"&H2:H&"| "&"| "&"| "&J2:J&","),)),",")),"|"))),"Select Col2,Col3, Col4,Col5,Col6,Col7,Col8,Col9,Col1,Col10")}

Related Topic