Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    hi all,

    i have a text string and i wan search for three words in it,"approved","replace',"moved" and assign say a number like 1 if any of these words exist.

    dudub
    TIA
    dubdub

  2. #2
    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
    If the cell is A1, this formula will display a 1 if any are in it and a 0 if none are in it:

    =OR(ISNUMBER(SEARCH("approved",A1)),ISNUMBER(SEARC H("replace",A1)),ISNUMBER(SEARCH("moved",A1)))*1

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Jul 2005
    Location
    Bahrain
    Posts
    373
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks Steve perfect.
    TIA
    dubdub

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say that your text string is in cell A1.

    Enter the list of search terms in a range of cells; let's say that H1 contains approved, H2 contains replace, and H3 contains moved.

    Enter the following array formula in cell B1 (confirm with Ctrl+Shift+Enter):

    =SUM(1-ISERROR(SEARCH($H$1:$H$3,A1)))

    This formula will return the number of matches, i.e. if none of the search terms occurs in the text string, it will return 0, if exactly one of the search terms occurs, it will return 1 etc.

    If you prefer to return TRUE if there is at least one match and FALSE if there is none, you can use


    =SUM(1-ISERROR(SEARCH($H$1:$H$3,A1)))>0

    again as an array formula.

Posting Permissions

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