Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    388
    Thanks
    3
    Thanked 0 Times in 0 Posts

    finding certain words in a cell and recording find (xp & 2003)

    I am attaching a sample worksheet. I would like to find a certain word or phrase in a cell and record one of three different words. In column E is the "Description" which contain different phrases. Column E is where I would like the formula to record one of 4 words 1. REVERSAL, 2.ACCRUAL 3. MONTHLY ENTRY & 4. OTHER depending on what the criteria in the Description Cell is written.

    Is this a possible task?
    Attached Files Attached Files

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: finding certain words in a cell and recording find (xp & 2003)

    Don't you mean you want column F to be where you record your formula result?

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

    Re: finding certain words in a cell and recording find (xp & 2003)

    Try this in F2, and fill down:

    =INDEX({"Reversal","Monthly Entry","Accrual","Monthly Entry"},MATCH(FALSE,ISERROR(SEARCH({"reversal","pa yroll j/e","accrual","adp payroll"},E2)),0))

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: finding certain words in a cell and recording find (xp & 2003)

    Hans,
    Very interesting! Would you mind explaining how you solution works?
    Thanks

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

    Re: finding certain words in a cell and recording find (xp & 2003)

    {"reversal","payroll j/e","accrual","adp payroll"} is an array containing the four search terms.

    SEARCH({"reversal","payroll j/e","accrual","adp payroll"},E2) tries to find each of the search terms in cell E2 (not case-sensitive) and returns a a 4 element array: a number if found, an error if not found.

    ISERROR(SEARCH({"reversal","payroll j/e","accrual","adp payroll"},E2)) returns a 4 element array: FALSE if found (SEARCH did *not* return an error), TRUE if not found.

    MATCH(FALSE,ISERROR(SEARCH({"reversal","payroll j/e","accrual","adp payroll"},E2)),0) returns the position of the first FALSE in the array (or #N/A if all values are TRUE).

    =INDEX({"Reversal","Monthly Entry","Accrual","Monthly Entry"},MATCH(FALSE,ISERROR(SEARCH({"reversal","pa yroll j/e","accrual","adp payroll"},E2)),0)) looks up the corresponding item in the array of return strings {"Reversal","Monthly Entry","Accrual","Monthly Entry"}.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: finding certain words in a cell and recording find (xp & 2003)

    Thanks Hans

  7. #7
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: finding certain words in a cell and recording

    Or try……..

    F2, enter the formula and copy down :

    =LOOKUP(1,-SEARCH({"reversal","payroll j/e","accrual","adp payroll"},E2),{"Reversal","Monthly Entry","Accrual","Monthly Entry"})

    Regards
    Bosco

Posting Permissions

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