SOLVED

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

Copper Contributor

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)

lucas_h_1-1586003976179.png

Any help is greatly appreciated!!

 

Lucas

 

6 Replies
best response confirmed by lucas_h (Copper Contributor)
Solution

@lucas_h Try this in A1 and copy it down.

=IF(ISNUMBER(SEARCH("yel",A1)),"Y",0)

 

=IF(ISTEXT(FIND("yel",A1)),0,"Y")

@lucas_h 

For the collection

=IF(COUNTIF(A1,"*yel*"),"Y","0")

@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")
Actually meant

=IF(ISERR(FIND("yel",A1)),0,"Y")

@Riny_van_Eekelen It worked!  Greatly appreciate your help.  

1 best response

Accepted Solutions
best response confirmed by lucas_h (Copper Contributor)
Solution

@lucas_h Try this in A1 and copy it down.

=IF(ISNUMBER(SEARCH("yel",A1)),"Y",0)

 

View solution in original post