Find specific text in cell

[s_1592]

Hi there,
I'm looking for a way to find specific text within a cell.

For instance, I need to look for the text "apple" in a cell.

If the cell contains the text "eat's apple" (i.e. whenever the word is uniquely "isolated"), then 1; if the cell contains the words "eat's apples" or "eat's an aapple", the result should be 0.

Your help would be greatly appreciated.
Thanks!

 

[Joe4]

For a value in cell A1:

=IF(ISNUMBER(SEARCH("eat's an apple",A1)),1,0)

(though I don't think "eat's" can ever be possessive, so it would be "eats"! ).

 

[s_1592]

For a value in cell A1:

=IF(ISNUMBER(SEARCH("eat's an apple",A1)),1,0)

(though I don't think "eat's" can ever be possessive, so it would be "eats"! ).

Indeed that was a typo on my end

I actually meant the following:

If, for instance, cell contains a specific text, then 1 (e.g. If I'm looking for the text "apple", then cell "eats an apple" = 1); if cell contains variations of the searched text (eg. in the example of "apple", cells "eats apples" or "eats an aapple" = 0).

I'm looking for a formula which works at scale, assuming thousands of cells to be checked, containing many different variations of the serched text, & multiple difference texts to be searched.

Hope that's clear.

Thanks for your help!

 

[Akuini]

I actually meant the following:

If, for instance, cell contains a specific text, then 1 (e.g. If I'm looking for the text "apple", then cell "eats an apple" = 1); if cell contains variations of the searched text (eg. in the example of "apple", cells "eats apples" or "eats an aapple" = 0).

I'm looking for a formula which works at scale, assuming thousands of cells to be checked, containing many different variations of the serched text, & multiple difference texts to be searched.

You need to use regex, utilizing "word boundary" & it means VBA, is that ok?

 

[s_1592]

You need to use regex, utilizing "word boundary" & it means VBA, is that ok?

I wasn't really planning on using VBA, as I'm not very comfortable with it, and was rather looking for a "classic" Excel formula.
However, if VBA is the only viable solution, then OK.

 

[Akuini]

I wasn't really planning on using VBA, as I'm not very comfortable with it, and was rather looking for a "classic" Excel formula.
However, if VBA is the only viable solution, then OK.

Sorry, I don't know how to do it with formula. Hopefully somebody will be able to help.

 

[Joe4]

I wasn't really planning on using VBA, as I'm not very comfortable with it, and was rather looking for a "classic" Excel formula.

For something as robust and complex as you want, I don't know if that is possible to create a "classic" Excel formula for it, unless you are willing to make a list of every possible thing/scenario you want to check for.

 

[s_1592]

For something as robust and complex as you want, I don't know if that is possible to create a "classic" Excel formula for it, unless you are willing to make a list of every possible thing/scenario you want to check for.

OK, that's noted.

As a classic Excel solution doesn't exist, I can try working with a VBA one.

I would however require a "for dummy", rather step-by-step guide as to how to implement the solution & properly play with it, I guess, as unfortunately I haven't really used VBA many times before.

That would be really helpful