If - easy question

M

mldancing

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! :-(
 
B

Billy Liddel

mldancing said:
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! :-(
Click to expand...
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
 
D

David Biddulph

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