In Excel, how to extract and convert unsorted comma-seperated data from one column into multiple columns?

In Excel, I have a table where rows correspond to the respondents of a survey and columns correspond to the respondents' answers to some questions.

When asked what the respondents grow on their fields, they could return a number of cultivars, like cereals, oilseeds, corn, etc.

Unfortunately, all answers to this question were entered into the excel sheet within one cell, seperated by a comma, but not ordered in any way. (A cell might also be empty.)

Column A	Column B
Respondent 1	Cereals,Oilseeds
Respondent 2	Oilseeds,Corn,Cereals
Respondent 3	Corn,Oilseeds

What I need is a unique column for each possible answer (I can do that by hand as the possible answers are not so many), where in each row there is a "1" if this string is contained in the original cell, and a "0" if the string is not contained in the original cell. The result should look like this:

Column A	Column B	Cereals	Oilseeds	Corn
Respondent 1	Cereals,Oilseeds	1	1	0
Respondent 2	Oilseeds,Corn,Cereals	1	1	1
Respondent 3	Corn,Oilseeds	0	1	1

I could not figure out any way to do this automatically ... Any help would be greatly appreciated!!!

Solution

Here is a dynamic array solution(alternative approach), it creates the headers dynamically and spills the 1s and 0s as well, it also checks ensures that it does not returns any FALSE POSITIVES:

• Formula used in cell C1

=LET(
     a, DROP(B1:B4,1),
     b, SEQUENCE(,MAX(LEN(a)-LEN(SUBSTITUTE(a,",",))+1)),
     c, UNIQUE(TOROW(TEXTSPLIT(TEXTAFTER(","&a,",",b),","),2),1),
     w, LAMBDA(x,y,N(ISNUMBER(FIND(","&INDEX(c,,y)&",",","&INDEX(a,x)&",")))),
     VSTACK(c,MAKEARRAY(ROWS(a),COLUMNS(c),w)))

Test Case One:

If you have the headers spitted separately then, try the following formula:

• Formula used in cell C2

=MAKEARRAY(ROWS(B2:B4),COLUMNS(C1:E1),LAMBDA(x,y,
 N(ISNUMBER(FIND(","&INDEX(C1:E1,y)&",",","&INDEX(B2:B4,x)&",")))))