Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    CONTAINS function (Excel 2002)

    Is there a formula that will produce a result for "contains"... for example, I have an "IF" formula that reads a cell containing multiple words. I'd like the formula to say "IF" cell A3 "CONTAINS" the word "ACCRUAL", then the result in cell C3 is "YES". I can't use the LEFT or RIGHT or MID formulas as the word "ACCRUAL" can be in any position in the narrative.
    Thanks!
    Lana

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: CONTAINS function (Excel 2002)

    You can use the FIND or SEARCH function for this. FIND is case-sensitive, SEARCH isn't. FIND and SEARCH both return a number if the search text is found, and #VALUE if not. You can use IF and ISERROR to translate this to "Yes" and "No" (or whatever you want).

    The formula to determine if ACCRUAL occurs in the value of cell A3 is
    <code>
    =IF(ISERROR(FIND("ACCRUAL",A3)),"No","Yes")
    </code>
    If ACCRUAL might be written as Accrual or accrual, use SEARCH instead of FIND.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: CONTAINS function (Excel 2002)

    Hi Lana

    Use the Find function in conjunction with the iserror, assume the string value containing the substring is in A1

    =IF(ISERROR(FIND("Accrual",A1,1))=FALSE,"True","Fa lse")


    Be aware that FIND is case sensitive
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Jul 2002
    Location
    Ankeny, Iowa, USA
    Posts
    344
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Re: CONTAINS function (Excel 2002)

    Thank you Hans & Jerry... I appreciate the help!
    Lana

Posting Permissions

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