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

Question

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

Alessandro · Accepted Answer · 2020-05-08 10:39:29Z

1

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)

answered May 8, 2020 at 10:39

Alessandro

3,0861 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

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), "♠")), "♦")))

spreadsheet demo

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered May 8, 2020 at 10:51

player0

131k14 gold badges91 silver badges149 bronze badges

Comments

MattKing · Accepted Answer · 2020-05-08 15:28:26Z

1

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

answered May 8, 2020 at 15:28

MattKing

7,80310 silver badges13 bronze badges

2 Comments

Nimantha Over a year ago

Hey Matt! Regarding the linked Google Sheet - it looks like the formulas in the sheet currently don't work as expected (displaying #ERROR!) as it looks like the original data was modified / removed....was about to restore to your original version (where the formulas work as intended), but then thought of just letting you know (in case you wanted to make any changes to the post, formula, etc.) ...also the FLATTEN function has since been official!

MattKing Over a year ago

Thanks @Nimantha! Indeed it has been made official! If you're curious about some more background on FLATTEN and how that came to be official, you can check out my Reddit post here which is partly about that. reddit.com/r/sheets/comments/rrdpoy/… I'll make the sample sheet View Only now.

Collectives™ on Stack Overflow

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

3 Answers 3

Approach

Comments

spreadsheet demo

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Approach

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related