Find specific value in a string

[Lost in Microbiology]

I need to find a specific number from a list of numbers within a range of
cells.

For example:

Column B
1
2
3
4
5
10,11
9,8
12
11

I need to find if 11 is in the list. Find if shows each cell with an "11"
but I can't get a function to work. I tried =countif(b$2:b$16,"11")>0 but it
doesn't work. I can treat the cells as a number or text, whichever will make
the function work. Basically, I am trying to fiind if a value exists in a
column and reutrn a yes/no to create an automatic proof reading tool.

 

[excelent]

=MATCH(11,A1:A100,0)

return row number


"Lost in Microbiology" skrev:

 

[Lost in Microbiology]

This only works if there is a single value. Any cell with a comma separated
field returns #N/A.

Even if I could find a conditional format that would highlight the cell that
contains the number.

 

[MartinW]

Hi Lost,

As there is text and numbers this should work
=COUNTIF(B$2:B$16,"*11*")+COUNTIF(B$2:B$16,11)

HTH
Martin

 

[MartinW]

Woops that should be

=IF(COUNTIF(B$2:B$16,"*11*")+COUNTIF(B$2:B$16,11)>0,"Yes","No")

HTH
Martin

 

[Teethless mama]

Try this:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH("11",A1:A9)))>0,"found","not found")

 

[Lost in Microbiology]

Thanks Martin, that seems to do the trick!

Until someone asks me to do this in a slightly different way, this looks
like the answer!

 

[Lost in Microbiology]

This was working, but now I am finding it will say "Yes" for a 1 if there is
a 11,12,13,etc. in the column. Is there a way around this? Should I change
the column to text?

Thanks

 

[MartinW]

I can't get it to return a "Yes" for anything but 11 in my trials.
How is your data setup where you get "Yes" for a 1.

I assume you have altered the formula to match your actual data
rather than the example you posted. Please post a copy of the new formula
along with an explanation of the nature of your data.

Regards
Martin