Formula to find a number in a string of numbers separated by a symbol

Hello,

I have column A that has entries looking like:
cell A1: 1|2|3|4|10|14
cell A2: 1|9|13
cell A3: 4
cell A4: 5|14
cell A5: 1
cell A6: 1|11
etc.

The numbers in each cell range from 1-14 and they are always in ascending order.

What I want to do is in Column B, return "yes" if the cell in A has the number 1 and return "no" if it doesn't.

I've tried using the FIND fxn, but it also counts "11" as a "yes". So I tried to FIND "1|". But this was problematic because cells with only the number "1" will return "no" because there is no "|" bar.

This gets tricky, because in Column C, I want to return "yes" if the cell in A has the number 2 and return "no" if it doesn't. Again, FIND fxn counts "12" as a "yes".

I've spent so much time on this problem and still can't figure it out. Your help is very much appreciated!

If you use Text to Columns with "|" as a separator you can simply use IF(COUNTIF(...)<>0,"yes","no")
Of course this will reformat your spreadsheet somewhat.

Try like this

=IF(ISNUMBER(FIND("|1|","|"&A1&"|")),"Yes","No")

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.