How do I keep all the values when I split a cell by delimiter (comma) in Google Sheets?

Question

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

There are two tabs, the first tab is the following:

The second tab is the following:

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?

Answer 1

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)

---

---

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

---

---

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

---

---

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

---

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")}

---

Answer 2

You want to break out the names of attendees for School, Restaurant and Park Locations. The resulting output should be a row for each attendee.

Your script is intended to run as a custom function. You said that your script splits the names in column G, but column H & I it only keeps the first attendee.

The reason that the names in the other Columns are not being dissected can be are found in these two sets of lines:

---

jLen=s.length:

• s is for Column G - the attendees for the Restaurant and this line is part of a loop; but there is no equivalent for the columns H & I

---

if(k == 6)     

 {
   output1.push(s[j]);
 }
 else
 {
   output1.push(range[i][k]);
  } 

---

• this section is a bit more obvious. If k=6 (restaurant), then the split names are added to the row array, but if k<>6, then the UNSPLIT values for Columns H & I are added.

---

There are many ways to answer your question. Please consider this as one alternative. The answer is somewhat more verbose than yours, but you can fine-tune that at your leisure. The answer is also written as a stand-alone function; again, you can convert it to a custom function.

---

function wa14339503() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataname = "ejbData"
  var source = ss.getSheetByName(dataname);
  var outputname = "ejbOutput";
  var output = ss.getSheetByName(outputname);

  // get details of data sheet
  var lRow = source.getLastRow();
  var lCol = source.getLastColumn();
  var datarange = source.getRange(1,1,lRow,lCol);
  var datavalues = datarange.getValues();
  var dataLen = datavalues.length
  // Logger.log("DEBUG: length of data = "+dataLen+", data range = "+datarange.getA1Notation());

  // create temporary array for output
  var output2 = [];

  // loop though the rows
  for (var i=0; i<datavalues.length; i++) {
    
    // row 0 = headers
    if (i===0){
      // create a temporary array for the row data
      var rowarray = [];
      
      // push data values to rowarray
      rowarray.push([datavalues[i][0]]);
      rowarray.push([datavalues[i][1]]);
      rowarray.push([datavalues[i][2]]);
      rowarray.push([datavalues[i][3]]);
      rowarray.push([datavalues[i][4]]);
      rowarray.push([datavalues[i][5]]);
      rowarray.push([datavalues[i][6]]);
      rowarray.push([datavalues[i][7]]);
      rowarray.push([datavalues[i][8]]);            
      rowarray.push([datavalues[i][9]]);      
      
      // push the row data to the output array
      output2.push(rowarray);
      // Logger.log("DEBUG: i="+1+", header row, length of output2 = "+output2.length);
    }
    
    // start testing data rows
    if (i>0){
      
      // reset row data
      var rowarray = [];
      
      // test for location
      if (datavalues[i][5].length>0){
        
        // get location value
        var location = datavalues[i][5];
        
        // assign Attendee column
        if (location==="School"){
          var attendeedCol=7
        }else if (location==="Restaurant") {
          var attendeedCol=6
        } else if (location==="Park") {
          var attendeedCol=8
        }
            
        // Logger.log("DEBUG: the attendees = "+datavalues[i][attendeedCol])
        
        // split the attendees
        var locsplit = datavalues[i][attendeedCol].split(", ");
        
        //Logger.log("DEBUG: locsplit start")
        //Logger.log(locsplit); // DEBUG
        //Logger.log("DEBUG: locsplit end")

        // get the number of attendees
        var locLen = locsplit.length;
        // Logger.log("DEBUG: the location = "+location+", and the number of persons = "+locLen);

        // for this data row, loop through the number of attendees to create new rows.
        for (var l=0; l<locLen;l++){
          
          // clear the row array
          var rowarray = [];
          
          // Logger.log("DEBUG: split value = "+locsplit[l]);
          
          // push values to row array
          rowarray.push([datavalues[i][0]]);
          rowarray.push([datavalues[i][1]]);
          rowarray.push([datavalues[i][2]]);
          rowarray.push([datavalues[i][3]]);
          rowarray.push([datavalues[i][4]]);
          rowarray.push([datavalues[i][5]]);
          rowarray.push([datavalues[i][6]]);
          rowarray.push([datavalues[i][7]]);
          rowarray.push([datavalues[i][8]]);            
          rowarray.push([datavalues[i][9]]);  
          
          // substitute the attendee name for the attendee listlist
          rowarray.splice(attendeedCol, 1, locsplit[l]);
                   
          // push the new row onto the array
          output2.push(rowarray);
          
          // rinse and repeat for the number of attendees in the split
        }
        
      }
      
    }
    
  }
  // Logger.log(output2); DEBUG
  // Logger.log("DEBUG: length of output2 = "+output2.length);
  
  // create a range on the output sheet
  // set the values with the temporary array
  output.getRange(1,1,output2.length,lCol).setValues(output2)
  
}

---

Before

---

After