Excel Formula to convert comma separated column values-CodePudding

I have the following list of two columns, which contains view names in column A and tables name seperated by comma in column B.

Want to convert the same in below format:

CodePudding user response：

Only with Excel365

=TEXTJOIN(", ";TRUE;FILTER($A$1:$A$2;ISNUMBER(SEARCH(D6;$B$1:$B$2))))

CodePudding user response：

in GS use:

=ARRAYFORMULA(SUBSTITUTE(TRIM(SPLIT(SUBSTITUTE(FLATTEN(QUERY(QUERY(
 TRIM(SPLIT(FLATTEN(A1:A&"×"&SPLIT(B1:B, ",")), "×")), 
 "select max(Col1) where Col2 is not null group by Col1 pivot Col2"),,9^9)), 
 " ", "×", 1), "×")), " ", ", "))