+ Reply to Thread
Results 1 to 4 of 4

Find status using Excel search function

  1. #1
    Registered User
    Join Date
    03-10-2009
    Location
    AC
    MS-Off Ver
    Excel 2003, 2007
    Posts
    16

    Find status using Excel search function

    Hi!
    I made this quite long formula and it works. Maybe there is a short formula/function that will give me the same result?
    Thanks for the help.

    Please Login or Register  to view this content.
    Last edited by Dyzone; 03-21-2012 at 08:55 AM.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find status using Excel search function

    Are you using ISNUMBER(SEARCH()) because the search string are substrings of the contents of J3784? If so, then you probably can't get much shorter, except for maybe combining the first to searches with same result:

    Please Login or Register  to view this content.
    If the strings are the exact contents of J3784, the you just need to check equality:

    Please Login or Register  to view this content.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    03-10-2009
    Location
    AC
    MS-Off Ver
    Excel 2003, 2007
    Posts
    16

    Re: Find status using Excel search function

    Thanks, great! NVBC as always.
    Both works for me.The second option is enough, since the strings are exact contents of each cell.

    No, I used ISNUMBER(SEARCH()) basically to check if it is text or number(correct me if i'm wrong),
    and thought it was a good approach to get a good result.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Find status using Excel search function

    ISNUMBER(SEARCH()) doesn't check if it is text or number in the cell, it checks first if the value is found in the cell, if it is SEARCH() returns a position number within the cell that the string is found in.. if not it returns a #VALUE! error... the ISNUMBER just checks if the SEARCH() returned a position number, indicating that somewhere in the string the match was found. ... in your case that position is always 1 if found, or #VALUE error if not. Since you have an exact match to find, then simply equality check is all you need.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1