Forum Discussion

Copper Contributor

Apr 04, 2020

Solved

Return value Y/N if text is within a cell.

Good morning Microsoft Tech Community,

I am fairly new to Excel and quite amazed at how powerful this tool can be. I figured there would be no better place to find an answer than here!

What function /formula would I use to acknowledge an "abbreviated" text is found within a cell

see below: (In this case I simply want to add a formula in B1 and drop down, a "Y" would signify "yel" is within the text and "0" would indicate it is not)

Any help is greatly appreciated!!

Lucas

excel

Riny_van_Eekelen
Apr 04, 2020
lucas_h Try this in A1 and copy it down.
=IF(ISNUMBER(SEARCH("yel",A1)),"Y",0)

6 Replies

SergeiBaklan
Diamond Contributor
Apr 04, 2020
lucas_h

For the collection

=IF(COUNTIF(A1,"*yel*"),"Y","0")
Abiola1
MVP
Apr 04, 2020
=IF(ISTEXT(FIND("yel",A1)),0,"Y")
- Riny_van_Eekelen
  Platinum Contributor
  Apr 04, 2020
  Abiola1 Not really! If found, it will return a number. If not, the result will be #VALUE!. Neither is a text, thus the result will always be "Y".
  
  Perhaps you meant:
  =IF(ISERROR(FIND("yel",A1)),0,"Y")
  - Abiola1
    MVP
    Apr 04, 2020
    Actually meant
    
    =IF(ISERR(FIND("yel",A1)),0,"Y")
Riny_van_Eekelen
Platinum Contributor
Apr 04, 2020
lucas_h Try this in A1 and copy it down.
=IF(ISNUMBER(SEARCH("yel",A1)),"Y",0)
- lucas_h
  Copper Contributor
  Apr 05, 2020
  Riny_van_Eekelen It worked! Greatly appreciate your help.