SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1281190%22%20slang%3D%22en-US%22%3EReturn%20value%20Y%2FN%20if%20text%20is%20within%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281190%22%20slang%3D%22en-US%22%3E%3CP%3EGood%20morning%20Microsoft%20Tech%20Community%2C%3C%2FP%3E%3CP%3EI%20am%20fairly%20new%20to%20Excel%20and%20quite%20amazed%20at%20how%20powerful%20this%20tool%20can%20be.%26nbsp%3B%20I%20figured%20there%20would%20be%20no%20better%20place%20to%20find%20an%20answer%20than%20here!%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20function%20%2Fformula%20would%20I%20use%20to%20acknowledge%20an%20%22abbreviated%22%20text%20is%20found%20within%20a%20cell%3C%2FP%3E%3CP%3Esee%20below%3A%26nbsp%3B%20%26nbsp%3B(In%20this%20case%20I%20simply%20want%20to%20add%20a%20formula%20in%20B1%20and%20drop%20down%2C%26nbsp%3B%20a%20%22Y%22%20would%20signify%20%22yel%22%20is%20within%20the%20text%20and%20%220%22%20would%20indicate%20it%20is%20not)%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22lucas_h_1-1586003976179.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F182125iFF00C4D50B111B3F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22lucas_h_1-1586003976179.png%22%20alt%3D%22lucas_h_1-1586003976179.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAny%20help%20is%20greatly%20appreciated!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELucas%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1281190%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281214%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20Y%2FN%20if%20text%20is%20within%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281214%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609092%22%20target%3D%22_blank%22%3E%40lucas_h%3C%2FA%3E%26nbsp%3BTry%20this%20in%20A1%20and%20copy%20it%20down.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(ISNUMBER(SEARCH(%22yel%22%2CA1))%2C%22Y%22%2C0)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281264%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20Y%2FN%20if%20text%20is%20within%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281264%22%20slang%3D%22en-US%22%3E%3DIF(ISTEXT(FIND(%22yel%22%2CA1))%2C0%2C%22Y%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281368%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20Y%2FN%20if%20text%20is%20within%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281368%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F609092%22%20target%3D%22_blank%22%3E%40lucas_h%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFor%20the%20collection%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(COUNTIF(A1%2C%22*yel*%22)%2C%22Y%22%2C%220%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281415%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20Y%2FN%20if%20text%20is%20within%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281415%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F421698%22%20target%3D%22_blank%22%3E%40Abiola1%3C%2FA%3E%26nbsp%3BNot%20really!%20If%20found%2C%20it%20will%20return%20a%20number.%20If%20not%2C%20the%20result%20will%20be%20%23VALUE!.%20Neither%20is%20a%20text%2C%20thus%20the%20result%20will%20always%20be%20%22Y%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPerhaps%20you%20meant%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIF(ISERROR(FIND(%22yel%22%2CA1))%2C0%2C%22Y%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1281449%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20Y%2FN%20if%20text%20is%20within%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1281449%22%20slang%3D%22en-US%22%3EActually%20meant%3CBR%20%2F%3E%3CBR%20%2F%3E%3DIF(ISERR(FIND(%22yel%22%2CA1))%2C0%2C%22Y%22)%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1283111%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20value%20Y%2FN%20if%20text%20is%20within%20a%20cell.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1283111%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BIt%20worked!%26nbsp%3B%20Greatly%20appreciate%20your%20help.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New 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
Highlighted
Best Response confirmed by lucas_h (New Contributor)
Solution

@lucas_h Try this in A1 and copy it down.

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

 

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

@lucas_h 

For the collection

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

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

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

@Riny_van_Eekelen It worked!  Greatly appreciate your help.