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

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"

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393921

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


"swatsp0p" wrote:


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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393921

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 text
will return "Not Found" (adjust this response as desired)

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

Good luck

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393921

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

Stuart


"swatsp0p" wrote:


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 text
will return "Not Found" (adjust this response as desired)

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

Good luck

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393921

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=393921