IF statement #8

is there a way to say to create a column that displays a Yes or No if part of 
a field has some text in it

for example if you wanted to tag the ones that had a D in column A

ColumnA             Tag
abc                     N
abde                   Y
abcd                   Y
Thanks!
0
HS (21)
6/8/2009 5:23:01 PM
excel.newusers 15348 articles. 2 followers. Follow

4 Replies
458 Views

Similar Articles

[PageSpeed] 45

=LEN(A1)<>LEN(SUBSTITUTE(A1,"d",""))   ......................... TRUE/FALSE
=IF(LEN(A1)<>LEN(SUBSTITUTE(A1,"d","")),"Y","N") ..........  Y/N
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"HS" <HS@discussions.microsoft.com> wrote in message 
news:628D66EF-BB41-48B6-8517-7BFE878ADCCC@microsoft.com...
> is there a way to say to create a column that displays a Yes or No if part 
> of
> a field has some text in it
>
> for example if you wanted to tag the ones that had a D in column A
>
> ColumnA             Tag
> abc                     N
> abde                   Y
> abcd                   Y
> Thanks! 


0
bliengme (657)
6/8/2009 5:41:03 PM
thank you :) I guess I should have gone a little further with this I'm trying 
to find a word in some text in the column so it's more like a few letters, to 
be exact it's OEM

for example
Calibre Micronic OEM Bnd SW

and i need to tag it cuz it has OEM in the text?

Is that possible?
Thanks!



"Bernard Liengme" wrote:

> =LEN(A1)<>LEN(SUBSTITUTE(A1,"d",""))   ......................... TRUE/FALSE
> =IF(LEN(A1)<>LEN(SUBSTITUTE(A1,"d","")),"Y","N") ..........  Y/N
> best wishes
> -- 
> Bernard V Liengme
> Microsoft Excel MVP
> http://people.stfx.ca/bliengme
> remove caps from email
> 
> "HS" <HS@discussions.microsoft.com> wrote in message 
> news:628D66EF-BB41-48B6-8517-7BFE878ADCCC@microsoft.com...
> > is there a way to say to create a column that displays a Yes or No if part 
> > of
> > a field has some text in it
> >
> > for example if you wanted to tag the ones that had a D in column A
> >
> > ColumnA             Tag
> > abc                     N
> > abde                   Y
> > abcd                   Y
> > Thanks! 
> 
> 
> 
0
HS (21)
6/8/2009 5:52:01 PM
Replace "D" with "OEM"
Did you try this?
best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"HS" <HS@discussions.microsoft.com> wrote in message 
news:CA643CCE-4CEC-4200-92E0-212D54F0569F@microsoft.com...
> thank you :) I guess I should have gone a little further with this I'm 
> trying
> to find a word in some text in the column so it's more like a few letters, 
> to
> be exact it's OEM
>
> for example
> Calibre Micronic OEM Bnd SW
>
> and i need to tag it cuz it has OEM in the text?
>
> Is that possible?
> Thanks!
>
>
>
> "Bernard Liengme" wrote:
>
>> =LEN(A1)<>LEN(SUBSTITUTE(A1,"d",""))   ......................... 
>> TRUE/FALSE
>> =IF(LEN(A1)<>LEN(SUBSTITUTE(A1,"d","")),"Y","N") ..........  Y/N
>> best wishes
>> -- 
>> Bernard V Liengme
>> Microsoft Excel MVP
>> http://people.stfx.ca/bliengme
>> remove caps from email
>>
>> "HS" <HS@discussions.microsoft.com> wrote in message
>> news:628D66EF-BB41-48B6-8517-7BFE878ADCCC@microsoft.com...
>> > is there a way to say to create a column that displays a Yes or No if 
>> > part
>> > of
>> > a field has some text in it
>> >
>> > for example if you wanted to tag the ones that had a D in column A
>> >
>> > ColumnA             Tag
>> > abc                     N
>> > abde                   Y
>> > abcd                   Y
>> > Thanks!
>>
>>
>> 


0
bliengme (657)
6/8/2009 5:57:06 PM
Hi,

here are some other ideas:

=IF(COUNTIF(A1,"*d*")>0,"Y","N")

or

=IF(COUNTIF(A1,"*"&F1&"*")>0,"Y","N")  

where F1 has whatever you want to search for

If you are willing to have the results display as True or False instead of 
Y, N

=COUNTIF(A1,"*d*")>0

You can search for any string by just replacing the d with whatever..

-- 
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"HS" wrote:

> is there a way to say to create a column that displays a Yes or No if part of 
> a field has some text in it
> 
> for example if you wanted to tag the ones that had a D in column A
> 
> ColumnA             Tag
> abc                     N
> abde                   Y
> abcd                   Y
> Thanks!
0
6/9/2009 4:04:01 AM
Reply:

Similar Artilces: