=IF(ISNUMBER(SEARCH.... the difference between 1 and 11

Question

I am trying to convert a single column of numbers in excel to multiple depending on the content.

e.g. Table 1 contains 1 column that contains 1 or more numbers between 1 and 11 separated with a comma. Table 2 should contain 11 columns with a 1 or a 0 depending on the numbers found in Table 1.

I am using the following formula at present:

 =IF(ISNUMBER(SEARCH("1",A2)),1,0)

The next column contains the following:

 =IF(ISNUMBER(SEARCH("2",A2)),1,0)

All the way to 11

 =IF(ISNUMBER(SEARCH("11",A2)),1,0)

The problem with this however is that the code for finding references to 1 also find the references to 11. Is it possible to write a formula that can tell the difference so that if I have the following in Table 1:

 2, 5, 11

It doesn't put a 1 in column 1 of Table 2?

Thanks.

Answer 1

If you want to eliminate "11" case, but this is all based on hardcoded values, there should be a smarter solution.

=IF(ISNUMBER(SEARCH(AND("1",NOT("11")),A2)),1,0)

Answer 2

A version of LS_dev's answer that will cope with 0...n spaces before or after each comma is:

=IF(ISNUMBER(SEARCH(", 1 ,",", "&TRIM(SUBSTITUTE(A2,","," , "))&" ,")),1,0)

The SUBSTITUTE makes sure there's always at least one space before and after each comma and the TRIM replaces multiple spaces with one space, so the result of the TRIM function will have exactly one space before and after each comma.

Answer 3

How about using the SUBSTITUTE function to change all "11" to Roman numeral "XI" prior to doing your search:

=IF(ISNUMBER(SEARCH("1",SUBSTITUTE(A2, "11", "XI"))),1,0)

Answer 4

Use, for list with just comma:

=IF(ISNUMBER(SEARCH(",1,", ","&A2&",")),1,0)

If list is separated with , (comma+space):

=IF(ISNUMBER(SEARCH(", 1,", ", "&A2&",")),1,0)