IF and FIND

[Revengerer]

Hi People,

2 parts to my problem

Part 1 - i'm trying to write a formula using the IF and FIND command.
=IF(FIND("Successful",C2,1),"Successful","0")
if C2 contains the word "Successful" then the command works.
if it doesn't it returns a #VALUE!, even if i enter a [value_if_false] ("0")
Possible entries for C2 would be: Successful, Incomplete, Cancelled, Failed

Part 2 - what i would really like is
=IF(FIND("Successful",C2,1),"Successful",(FIND("Failed",C2,1))

i know that i'm doing something wrong i just don't know what. also i wouldl ike to be able to have this as a formula rather than a VBS ect

thanks in advance
Gavin

 

[Datsmart]

Try this:

=IF(OR(C2="Successful",C2="Incomplete",C2="Cancelled,",C2="Failed",),"Successful","not found")

 

[Revengerer]

thanks for the quick response.

C2 does not only have the word successful which is why i was trying to use the FIND command. below are examples of the full data of C2

SWMSP01: [JobID:621 ] Backup Operation Successful.
SWNWC01: [JobID:1389 ] Backup Operation Failed.
"SWNWP01: [JobID:309 ] Backup Operation Incomplete. Number of Error(s)/Warning(s): 0/2

hope this isn't too confusing

 

[Datsmart]

Try this long formula:

=IF(ISNUMBER(FIND("Successful",C2,1)),"Successful",IF(ISNUMBER(FIND("Incomplete",C2,1)),"Incomplete",IF(ISNUMBER(FIND("Cancelled",C2,1)),"Cancelled",IF(ISNUMBER(FIND("Failed",C2,1)),"Failed","None"))))

It inputs "None" if all tests fail.

 

[Revengerer]

Thanks heaps John,

i was trying to work on a similar formula but this has nailed it on the head.

thanks for all your help

 

[ExcelChampion]

This is a little longer but may in fact be easier to edit and add more words.

Assumes data starts in C1.

=IF(ISNA(MATCH(1,IF(FIND({"Successful","Failed","Incomplete","Cancelled",None},C1),ROW($A$1:$A$5)))),"None",CHOOSE(MATCH(1,IF(FIND({"Successful","Failed","Incomplete","Cancelled","None"},C1),ROW($A$1:$A$5))),"Successful","Failed","Incomplete","Cancelled","None"))

As well FIND is case sensitive. If this could cause issues, then:

=IF(ISNA(MATCH(1,IF(FIND({"successful","failed","incomplete","cancelled","none"},LOWER(C1)),ROW($A$1:$A$5)))),"Failed",CHOOSE(MATCH(1,IF(FIND({"successful","failed","incomplete","cancelled","none"},LOWER(C1)),ROW($A$1:$A$5))),"Successful","Failed","Incomplete","Cancelled","None"))

HTH!