Results 1 to 7 of 7
  1. #1
    Lounger
    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?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

  5. #5
    Gold Lounger
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

Posting Permissions

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