excelexcel-formulaextract# 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 `1`

s and `0`

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

- Build vertical summary from Columns with Count
- Excel - Create a Unit Conversion sheet - Multiple links
- update cell when column header matches a list
- Delete text in cell subject to Worksheet_Change
- Counting the number of visible rows after autofilter
- Excel 2007: AVERAGEIF, SUMIF, COUNTIF, MAXIF, MINIF across multiple sheets, multple rows
- Import CSV data from a txt file skipping the first line and adding headers
- Simplifying SumIFs formulas for efficient excel formula
- I'd like to find out how to find the current streak of non-negative numbers in a row of data in Excel
- ValueError: Invalid character found in sheet title
- decrypt excel files
- Excel table search funcion that looks for partial string match
- Combine macros to filter on today's and tomorrow's date
- Converting line breaks to commas in excel sheet using Powershell
- Combinations of numbers arranged side by side as many as a random number (VBA)
- How can I remove ONLY leading and trailing spaces while leaving spaces in between words alone with an excel formula?
- VBA Date as integer
- Sliding Window Auto Increment Range
- Count cells with different conditional ranges
- Turning flattened pivots data into tables -Error tables can't overlap - Excel Vba
- How do I get only a specific part of a cell in Excel or Numbers?
- Calculate the Legendre symbol of two integers in Excel
- Unable to get text wrapping or vertical centering to work with xlsxwriter
- Number stored as text warning in excel using POI
- VBA Excel paste to the columns in regular intervals
- Convert filtered values from formulas to values
- Sorting Dictionary by nested class objects data [VBA]
- Events does not appear when making an Outlook query from Excel
- The script works well when I use MSXML2.XMLHTTP.6.0, but it fails miserably when I switch to MSXML2.serverXMLHTTP.6.0
- How to convert text string (d h m s) to time format in excel