Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    IF statement locating text string (2002 SP-2)

    Is there a way to create an IF statement to locate a string of text?

    I've tried ^=IF(DB2 =*offender*,"Offender",IF(DB2 =* arrest*,"Arrest ", IF(DB2=*assault*,"Assault",IF(DB2 =*Restrain*,"Restrain",IF(DB2 =*dog*,"Dog","")))) and excel doesn't seem to like the *'s.

    I have a comment field and I want to be able to identify certain situations and by isolating only those comments that contain the above mentioned words. I thought that by inserting the above formula, I could then pivot the results to get just the items I want.

    I've tried searching excel help to no avail and only found
    ^=IF(ISERR(SEARCH("solo",D1)),1,0)
    on this site, which doesn't really support the multiple words I am looking for.

    Does anyone have any suggestions as to what I might try?

    Thanks

  2. #2
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: IF statement locating text string (2002 SP-2)

    Hi Capri,

    You can test for the presence of one of a number of strings within another like this:
    =IF(NOT(ISERROR(FIND("offender",DB2))),"Offender", IF(NOT(ISERROR(FIND("arrest",DB2))),"Arrest",IF(NO T(ISERROR(FIND("restrain",DB2))),"Restrain",IF(NOT (ISERROR(FIND("dog",DB2))),"Dog",""))))
    Note: the FIND function isn't case sensitive - use the SEARCH function if you need a case-sensitive test.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  3. #3
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: IF statement locating text string (2002 SP-2)

    Thanks

    However I tried your formula and every cell even those containing the words return blanks.
    I don't want case sensitive as the comments could be any combination of upper and lower case.

  4. #4
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: IF statement locating text string (2002 SP-2)

    Hi capri,

    The formula works as expected for me, using cell DB2 as the test cell. If any of those strings appears, the first one found by the order in which they appear in the IF test is returned, otherwise the result is blank.

    Are you sure you've got the correct cell references?

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: IF statement locating text string (2002 SP-2)

    Did you try the function replacing FIND with SEARCH? FIND actually is case sensitive, whereas SEARCH is not.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: IF statement locating text string (2002 SP-2)

    <hr>Note: the FIND function isn't case sensitive - use the SEARCH function if you need a case-sensitive test.<hr>
    It's the other way round - FIND is case sensitive, SEARCH isn't.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: IF statement locating text string (2002 SP-2)

    <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
    Cheers,

    Paul Edstein
    [MS MVP - Word]

Posting Permissions

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