If - easy question
 

I thought it was easy but it couldn't work:

I want it to show whatever in A1 if A1 doesn't have a word "cancel" in it:

if(A1="*cancel*",0,A1)

But it didn't work! :-(

If - easy question
 

trythis
if(A1<"cancel",A1)
--
cruchnin numbers


"mldancing" wrote:

I thought it was easy but it couldn't work:

I want it to show whatever in A1 if A1 doesn't have a word "cancel" in it:

if(A1="*cancel*",0,A1)

But it didn't work! :-(

If - easy question
 

or maybe this one

=IF(A1="cancel",A1,A1)

"mldancing" wrote:

I thought it was easy but it couldn't work:

I want it to show whatever in A1 if A1 doesn't have a word "cancel" in it:

if(A1="*cancel*",0,A1)

But it didn't work! :-(

If - easy question
 

Try this

=IF(A1<"",A1,"cancel")

"mldancing" wrote:

I thought it was easy but it couldn't work:

I want it to show whatever in A1 if A1 doesn't have a word "cancel" in it:

if(A1="*cancel*",0,A1)

But it didn't work! :-(

If - easy question
 

"mldancing" wrote:

I thought it was easy but it couldn't work:

I want it to show whatever in A1 if A1 doesn't have a word "cancel" in it:

if(A1="*cancel*",0,A1)

But it didn't work! :-(

If "Cancel" is just part of the text try this

=IF(FIND("CANCEL",UPPER(A1))0,A1,"")

Upper function just checks it is written in the same case.

Regards
Peter

If - easy question
 

Firstly, Peter, if you want to make the search case-insensitive, it's easier
to use SEARCH, rather than combining FIND and UPPER.
Secondly, your formula returns a #VALUE error if the string isn't found.
Thirdly, the OP said he wanted to show whatever is in A1 if A1 *doesn't*
have "cancel" in it, and your formula tries to do it the other way round.

I would recommend changing your formula to something like
=IF(ISNUMBER(SEARCH("cancel",A1)),0,A1) or
=IF(ISNUMBER(SEARCH("cancel",A1)),"",A1)
--
David Biddulph

If "Cancel" is just part of the text try this

=IF(FIND("CANCEL",UPPER(A1))0,A1,"")

Upper function just checks it is written in the same case.

Regards
Peter

"Billy Liddel" wrote in message
...


"mldancing" wrote:

I thought it was easy but it couldn't work:

I want it to show whatever in A1 if A1 doesn't have a word "cancel" in
it:

if(A1="*cancel*",0,A1)

But it didn't work! :-(