# Microsoft Office Application Help - Excel Help forum > Excel Formulas & Functions >  >  If cell contains...

## Tim Sampson

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

----------

hi
=IF(A1="Fred",1,IF(A1="Freddy",1,if(A1="Alfredo",1,0)))

>-----Original Message-----
>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
>.
>

----------


## Peo Sjoblom

=--(ISNUMBER(SEARCH("fred",A1)))

--

Regards,

Peo Sjoblom

"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

----------


## Tim Sampson

<anonymous@discussions.microsoft.com> wrote in news:0fae01c51438$df781480
$a601280a@phx.gbl:

> hi
> =IF(A1="Fred",1,IF(A1="Freddy",1,if(A1="Alfredo",1,0)))

Thanks but that's not quite what I was asking for. I might not know all the
different permutations. I want contains "fred".

Tim

----------


## Ken Wright

=--(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

----------


## Tim Sampson

"Peo Sjoblom" <terre08@mvps.org> wrote in news:usYMxjDFFHA.3492
@TK2MSFTNGP12.phx.gbl:

> =--(ISNUMBER(SEARCH("fred",A1)))
>

ISNUMBER, SEARCH and FIND all added to my portfolio.

Thanks very much
Tim

----------


## Tim Sampson

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

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

What is the =-- for?

Tim

----------


## 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

--
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

----------


## sdalgleish

> 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

----------


## taxman

How would I do If an cell contains "-" then do =LEFT(B2,FIND("-",B2)-1) else b2

----------


## daddylonglegs

_Your post does not comply with Rule 2 of our Forum_ RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

----------

