Google Sheets – Splitting a List in a Cell and Adding Text from Another Cell

google sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I am using Google Forms to collect information. In google sheet, I have rows of data and one of the cells contains lists that needs to be separated out.

=query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,char(10))))),"where Col1 is not null",0)

This Formula Works to Split Multiple entries from a single cell(G2:G) into a list

I would like to have the "Title"(B2:B) of the responses collected to be combined with the entries that were split form the "List"(G2:G)

=query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,char(10))&B2:B))),"where Col1 is not null",0)

This does split the List in the cells, but adds the title directly to the last character of the list item. Also, it adds more rows of just the "Title"(b2:b)

=query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,char(10))&" - "&B2:B))),"where Col1 is not null",0)

This Gives me error message: Result was not automatically expanded, please insert more rows (454494).

I also tried to use the working SPLIT Formula(Q2:Q) from above, and used index and match with it/

=ArrayFormula(iferror(index($B$2:$B, Match("*"&Q2:Q&"*",$G$2:$G,0),1)&" - "&Q2:Q ,""))

This returned the "Title" in B2 combined with all the split entries. If Failed to recognize the other "Title"

=arrayformula(if(Q2:Q="","",Match("*"&Q2:Q&"*",$G$2:$G,0)))

I split out the match formula and it does return the proper row # of the "Title" where the SPLIT List Entries came from.

Here is a sample Google Sheet
https://docs.google.com/spreadsheets/d/10gSSB4RE8049OlTh-FagxrZ2Bg0Mt7o0n4OE-gr-0IM/edit?usp=sharing

The outcome I am Looking for is to have the "Title"B2:B combined in the entries of that were SPLIT from "List" G2:G

Best Answer

Please see my comment added directly to your post.

In the meantime, I will take a guess that the following might be what you want:

=ArrayFormula(FLATTEN(FILTER(SPLIT(SUBSTITUTE(CHAR(10)&G2:G,CHAR(10),"~"&B2:B&" - "),"~"),B2:B<>"")))

(which should be placed in the Row-2 cell of a column that is otherwise empty from there down).

Addendum (based on comments):

Your posted data is in parallel form. That is, each person has only and exactly three food items associated. If that kind of exact parallel will not exist in your real data, it's best to rule out the blanks from the virtual grid that is formed before FLATTEN in the following way:

=ArrayFormula(QUERY(FLATTEN(FILTER(SPLIT(SUBSTITUTE(CHAR(10)&G2:G,CHAR(10),"~"&B2:B&" - "),"~"),B2:B<>"")),"Select Col1 WHERE Col1 Is Not Null"))

Related Topic