In Excel how can I convert a list of numbers (e.g. 1, 3, 7) in one cell to dummy variables (1s) in adjacent cells?

Question

I have been given an Excel spreadsheet with columns extracted from an online survey that contain lists of integers for options that participants have ticked. E.g. one participant may have ticked options 1, 6 and 11 in the survey so the cell contains [1,6,11]. Another just option 2. The range of possible values is from 1 to 11 in some cases, and 1 to 18 in other cases.

I need to transform these lists into 18 adjacent columns that contain a 1 if that column's number was present and 0 if it was not. Doing this manually would be extremely tedious and error prone.

The 11 or 18 adjacent columns already exist in the Excel spreadsheet.

I have tried using, for example:

=IFERROR(IF(FIND("2",A1), 1, 0), 0)

to place a "1" in the Option2 column if 2 is present in the list, and 0 if it is not.

This partially works but there are issues with the ambiguity of "1", "1,11" and "11".

I would appreciate thoughts on how best to achieve this.

The following image shows example of the desired result, but done manually...

Answer 1

You need to wrap the number you are checking for, and the string you are looking in, in commas (,) to avoid ambiguity.

There are several ways of producing a 1 for a match - my personal preference is to use Isnumber to see if the match is true or false, then the double unary minus to convert the result to a one or a zero:

=--ISNUMBER(FIND(","&COLUMN(A1)&",",","&$A2&","))