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

Casino Guy · 08-08-2005, 12:05 PM

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** · 08-08-2005, 12:36 PM

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

Casino Guy · 08-09-2005, 11:05 AM

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
>
>

**swatsp0p** · 08-09-2005, 12:39 PM

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

Casino Guy · 08-09-2005, 04:05 PM

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
>
>

**swatsp0p** · 08-10-2005, 09:02 AM

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