# Thread: If Cell Contains 'cancel' make 1 , if not '' (XP Excell)

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

2. ## 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)

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

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

5. ## 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,"")

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

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•