 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

6 Replies
Best Response confirmed by lucas_h (New Contributor)
Solution

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

@lucas_h Try this in A1 and copy it down.

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

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

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

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

For the collection

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

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

@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")``

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

Actually meant

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

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

@Riny_van_Eekelen It worked!  Greatly appreciate your help.