Search for text within Cell with Comma Seperated values

[Ronanm]

Hi

I have searched and found solutions, but none worked with my problem.

I have a Column of values E.G. In Column "B" Cell B2 will have 32,57,39. Column A Cell A2 will have a single number E.G. 57. If this is the case, Column C2 should contain "True".

I have tried "(--ISNUMBER(SEARCH(", "&A2&",",B2)>0)" and ISNUMBER(SEARCH(",A2,",","&B2&",")) etc.. but to no avail...


Thanks

 

[sheetspread]

Why do you need to search the commas? Isn't:

Excel 2010
	A	B	C
1	32,57,39	57	57
Sheet29

Cell Formulas
Range	Formula
C1	=IF(ISNUMBER(SEARCH(B1,A1)),B1,"")

good enough? (maybe not...)

 

[Special-K99]

=isnumber(search(b2&",",a2&","))

You may have problems searching for single digit numbers
Or even 3 or more digit numbers that contain smaller numbers.

ie

searching for 57 in a group that contains 113, 257, 341
doesn't contain 57 but will return TRUE due to 257.

 

[Rick Rothstein]

=isnumber(search(b2&",",a2&","))

You should put commas on each side so you don't match a larger number ending with the searched for number (plus you can use FIND instead of SEARCH as there is no upper/lower case text to worry about)...

=ISNUMBER(FIND(","&B2&",",","&A2&","))

 

[Ronanm]

Hi and thanks all

Rick I used ISNUMBER(FIND(","&B2&",",","&A2&",")) but didn't work if any spaces within the comma separated values, but added Substitute ISNUMBER(FIND(","&B2&",",","&SUBSTITUTE(A2," ","")&","))

Cheers