Find exact match and partial within same cell

[agoraotro]

Hi everyone,

I've been struggling to find a way to use a single formula to look for a text within a cell and, in another case, an exact match in that same cell.

This is an example:

Case 1= if A1 contains "red apples, apples, pears, grapes", value is TRUE
Case 2= if A1 contains "red apples, pears, grapes", value is FALSE
Case 3= if A1 contains "apples", value is TRUE

I would like B1 to display "OK" when the value is true as shown in the cases above. I want the formula to state the value is true only when the item 'apples' is present in the cell, and not others like 'red apples'.

I have tried with these formulas so far:

=IF(OR(ISERROR(FIND("apple",A1,1))),"","OK")

will be fine for case 1 and 3, but not 2

=IF(OR(ISERROR(FIND("apple,",A1,1))),"","OK")

will be fine for case 1 and 2, but not 3

Any ideas?

Thanks in advance

 

[Snakehips]

agoraotro,

Welcome to MrExcel.

Does this cover all situations?

Excel 2007
	A	B
1	red apples, apples, pears, grapes	OK
2	red apples, pears, grapes
3	apples	OK
4	apples, grapes red apples	OK
5	pears, red apples
6	pears
7	pears, apples	OK
8
Sheet1

Cell Formulas
Range	Formula
B1	=IF(OR(A1="apples",LEFT(A1,7)="apples,",RIGHT(A1,8)=", apples"),"OK",IF(OR(ISERROR(FIND(", apples,",A1,1))),"","OK"))

Hope that helps.

 

[oldbrewer]

if A1 contains red apples apples you cannot distinguish between them, unless you know red onlyoccurs in front of apples....

 

[agoraotro]

It does cover all situations.

Thank you very much, Snakehips.

 

[FormR]

Just another option that you could try:

Excel Workbook
	A	B
1	red apples, apples, pears, grapes	OK
2	red apples, pears, grapes
3	apples	OK
4	apples, grapes red apples	OK
5	pears, red apples
6	pears
7	pears, apples	OK
Sheet1