Results 1 to 7 of 7

20060215, 20:27 #1
 Join Date
 Mar 2004
 Location
 Albany, New York, USA
 Posts
 26
 Thanks
 0
 Thanked 0 Times in 0 Posts
If Cell Contains 'cancel' make 1 , if not '' (XP Excell)
Hello All,
I'm trying to sort a stack of info to see if cells in a column contain "Cancel" within any part of the cell. The problem is Cancel may appear first or after a few words, so i can't do a "lookleft" type If. After I do the If statement I'll use a Countif to tally them up to determine how many cells needed to be canceled.
If(A1 contains "cancel",1,"")
If(A2 contains "shipped",2,"")
and so on.
Any advice?

20060215, 20:35 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: If Cell Contains 'cancel' make 1 , if not '' (XP Excell)
Not sure why you switch from "cancel" to "shipped" and from 1 to 2, but see if you can adapt the formula
=IF(ISERROR(FIND("cancel",A1)),0,1)
to your needs.

20060216, 13:28 #3
 Join Date
 Mar 2004
 Location
 Albany, New York, USA
 Posts
 26
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: If Cell Contains 'cancel' make 1 , if not '' (XP Excell)
OK, that works great. My next question is how can I make that formula look for multiple objects..
`=IF(ISERROR(FIND("cancel",B1),1,(FIND("Shipped",B 1),2,(FIND("Held",B1),0,3)
obviously it's not working the way I have it but translated I need it to be:
if it finds Cancel give 1, if it gives shipped give 2, if it gives held, give 3.
Can that all be done in one formula or will I need multiple columns for each? I've attached to sheet that I need this to apply to.

20060216, 13:48 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: If Cell Contains 'cancel' make 1 , if not '' (XP Excell)
You can use this formula in row 2:
<code>
=IF(ISERROR(FIND("Shipped",D2)),IF(ISERROR(FIND("H eld",D2)),IF(ISERROR(FIND("Cancel",D2)),0,1),2),3 )
</code>
and fill down as far as needed.
Note: calculation is set to manual in your workbook, so you'll have to select the cells and press F9 to update their values.

20060216, 13:50 #5
 Join Date
 Feb 2004
 Location
 Cape Town, RSA
 Posts
 3,444
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: If Cell Contains 'cancel' make 1 , if not '' (XP Excell)
Try: =IF(ISERROR(FIND("Cancel",B1)),1,IF(ISERROR(FIND(" Shipped",B1)),2,IF(ISERROR(FIND("Held",B1)),3,"")
Regards,
Rudi

20060216, 13:54 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: If Cell Contains 'cancel' make 1 , if not '' (XP Excell)
=IF(ISNUMBER(FIND("cancel",B1)),1,IF(ISNUMBER(FIND ("Shipped",B1)),2,IF(ISNUMBER(FIND("Held",B1)),3,0 )))
If you do not want it case sensitive then use:
=IF(ISNUMBER(SEARCH("cancel",B1)),1,IF(ISNUMBER(SE ARCH("Shipped",B1)),2,IF(ISNUMBER(SEARCH("Held",B1 )),3,0)))
Steve

20060216, 14:09 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
Re: If Cell Contains 'cancel' make 1 , if not '' (XP Excell)
That won't work (apart from the two missing closing parentheses): if Cancel is not found, this formula will always return 1, without looking for Shipped or Held.