If cell contains...

=--(COUNTIF(A1,"*fred*")>0)

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Tim Sampson" <mail@get.rid.of.this.the-sampsons.co.uk> wrote in message
news:Xns95FF967D23551timsampsonessexuk@213.210.46.2...
> Hi
>
> I'm sure this must be the easiest question ever but it's proving very
> difficult for me to find in the help and on Google.
>
> What is the Excel function that says "if A1 contains "fred" then 1, else
> 0".
>
> Note, A1 might contain Fred, Freddy or Alfredo, all should match.
>
> TIA
> Tim

"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in news:#LEJasDFFHA.4072
@TK2MSFTNGP10.phx.gbl:

> =--(COUNTIF(A1,"*fred*")>0)
>

What is the =-- for?

Tim

Without it the formula will return TRUE or FALSE. The -- will convert TRUE
to 1 and FALSE to 0

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Tim Sampson" <mail@get.rid.of.this.the-sampsons.co.uk> wrote in message
news:Xns95FF9CE65B9FFtimsampsonessexuk@213.210.46.2...
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in
news:#LEJasDFFHA.4072
> @TK2MSFTNGP10.phx.gbl:
>
> > =--(COUNTIF(A1,"*fred*")>0)
> >
>
> What is the =-- for?
>
> Tim

Originally Posted by Ken Wright

Without it the formula will return TRUE or FALSE. The -- will convert TRUE
to 1 and FALSE to 0

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Is there a way to change this from 1/0 to something else? For example, I'd like to have something like a YES if its true but just a blank cell if false.

Thanks