CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query
Jason Nockels
Jason Nockels

Reputation: 11

Split Comma Separated Values in a String against ID with Google Sheets

Sample Google Sheet

I have some data that lives in a Google Sheet and is connected to Google Data Studio. Some of this data is comma separated within a string. Above is a link to a sample of some of this data.

I’ll try to explain the best I can but this video of how it’s resolved using Power Query in Excel is EXACTLY what I am trying to do, only in Google Sheets. Instructional Video on YouTube

In the above sample sheet, I have a respondent ID in column A in the ‘Data’ tab and I have the comma separated string in column B of the ‘Data’ tab.

In the ‘Solution’ tab in column B you’ll see I used the following code to split and transpose the contents of column B from the ‘Data’ tab to the ‘Solution’ tab. =transpose(split(join(" ",arrayformula(Data!B2:B&",")),",")) which worked well for splitting out the values into their own rows within that column

But what I need is the ID to be mapped against the respective values in column A in the ‘Solution’ tab. For the life of me, I cannot figure this out.

Any help would be appreciated. Thank you!

Update:

I also found this script for a custom function that I think will work but I am not sure how to get this to work. I get an error saying “TypeError: cannot read property length...”

function myFunction(range) {
delimiter = ", ";
targetColumn = 1;
targetColumn2 = 2;

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

Upvotes: 0

Views: 2038

Answers (3)

MattKing
MattKing

Reputation: 7773

There is a simple solution to these problems with the recent discovery of a new function hidden in the back end of Google sheets called FLATTEN(). I also made a copy since your shared sheet was view only. Link to Copy.

On a new tab this formula:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN(Data!A2:A&"|"&TRIM(SPLIT(Data!B2:B,","))),"|",0,0),"where Col2 is not null"))

Slightly more complicated, but parsing into 3 instead of 2 columns on Alt.Solution tab.

=ARRAYFORMULA(QUERY(SPLIT(SUBSTITUTE(FLATTEN(Data!A2:A&" ("&TRIM(SPLIT(Data!B2:B,","))),")","")," (",0,0),"where Col3 is not null"))

Upvotes: 1

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(IFERROR(SPLIT(B2:B, ","))="",,"♠"&A2:A&"♦"&SPLIT(B2:B, ",")))
 ,,99^99)),,99^99), "♠")), "♦")))

0

spreadsheet demo

Upvotes: 1

Alessandro
Alessandro

Reputation: 2998

Approach

I would use a custom function here:

/**
* @customfunction
*/
function covid(range) {
  let delimiter = ", ";
  let newRows = [];
  for (let i in range) {
    let row = range[i];
    let id = row[0];
    let strings = row[1].split(delimiter); // split the responses
    for (let j in strings) {
      newRows.push([id, strings[j]]); // links the responder id to its splitted response
    }
  }
  return newRows; // prints the rows
}

The input parameter is a A1 Notation range in your case it would be: =covid(Data!A2:B383)

Upvotes: 1

Related Questions