search a string of numbers for a number

[klatlap]

I have a string similar to this 1,9,7,11,8,10

I need a formula that would find 11 , the string can be different lengths so the number is not always in the same position.

 

[jtakw]

Hi,

So what do you want happened (result) once the formula finds it in the Text string, and what if it isn't there ?

 

[DanteAmor]

But what do you want to result? a label that says "Yes it exists"

=IF(IFERROR(SEARCH("11",A1),0)=0,"Does not exist", "Yes it exists")

 

[klatlap]

so the above formula can see the difference between 11 and 1?

 

[jtakw]

Use B1 formula if you want the value to look for (i.e. 11) hard-coded in the formula.
Use C1 formula if you might want to look for different values using a cell reference.

Book1
	A	B	C	D	E	F
1	1,9,7,11,8,10	Yes	Yes		Find What	11
2	1,9,111,7,11,8,10	Yes	Yes
3	1,9,111,7,112,8,10	No	No
4	11,9,7,110,8,10	Yes	Yes
5	1,9,7,211,8,10,11	Yes	Yes
Sheet586

Cell Formulas
Range	Formula
B1	=IF(ISNUMBER(FIND(",11,",","&A1&",")),"Yes","No")
C1	=IF(ISNUMBER(FIND(","&F$1&",",","&A1&",")),"Yes","No")

Formulas will Not error for 1, 111, 211, 112, etc., formula will Only result "Yes" if 11 exist within string.

 

[klatlap]

cheers jtakw this worked well.

 

[DanteAmor]

so the above formula can see the difference between 11 and 1?

Try this:

=IF(IFERROR(SEARCH(",11,",A1),0)=0,"No", "Yes")

 

[jtakw]

@ DanteAmor, That won't work if the 11 is in the beginning or end of the string, you must Also include commas before and after the cell containing the string.

 

[jtakw]

cheers jtakw this worked well.

You're welcome.

 

[DanteAmor]

@ DanteAmor, That won't work if the 11 is in the beginning or end of the string, you must Also include commas before and after the cell containing the string.

@jtakw, You're right

Use this:

=SI(SI.ERROR(HALLAR(",11,", ","&A1&","),0)=0,"No", "Yes")