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!!!


  • 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:

    enter image description here

    • Formula used in cell C1

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

    Test Case One:

    enter image description here

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

    enter image description here

    • Formula used in cell C2