Using 'If' refer to specific words in a cell containing text

[Guest]

Using the 'If' operator I want to refer to a cell containing a text string
but I only want to reference specific words in the text string.

Example: If I refer to a cell containing the text string "Friday Food Sales"
I want to reference only the words "Food Sales"

 

[swatsp0p]

Is this what you are looking for?:

A1="Friday Food Sales"

B1=IF(FIND("Food Sales",A1),"Found","Not Found")

Result in B1: "Found"


You don't indicate how you want to 'reference' the found string.

HTH

Bruce

 

[Guest]

The function you wrote works when the condition is true but when the
condition is false it returns #VALUE! instead of Not Found

 

[swatsp0p]

Sorry, guess I didn't test my formula well enough... try this:

=IF(ISNUMBER(FIND("Food Sales",A1)),"Found","Not Found")

Blank cells or numeric cells or text cells not containing the FIND tex
will return "Not Found" (adjust this response as desired)

Let me reiterate that FIND is CasE SenSitiVe. "Sales" <> "sales"

Good luck

Bruc

 

[Guest]

Thank you. It works perfectly now. I still don't quite understand why the
first version didn't work.

Stuart

 

[swatsp0p]

To understand the problem, we need to know what FIND does. It returns
the location number of the first character in the FIND. If it does not
find the value, it returns an error, hence the need to verify that it
returns a number (with ISNUMBER).

Therefore, the formula says that if we have FIND return a number
(meaning it found the value) then tell me you found it, if not, tell me
you didn't find it.

Clearer, now?

Bruce