Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    #VALUE! to be replaced (97)

    #VALUE! normally comes up when an error occurs. But SEARCH returns #VALUE! as a normal function, not as an error.
    SEARCH returns a number representing a position if the string is found, but returns #VALUE! if not found. I expect a zero if not found. But I can't change that, so ......here is the problem:

    I am checking for a string in another string and I want to have value of 1 if found, and 0 or blank if not found The position is not important, the existence anywhere should give a 1.
    Normally I am using references to text cells, but using actual text for testing and help here.

    I am using the following formula: =IF(SEARCH("a","Maine")>0,1,"") which resuls in a 1,
    but =IF(SEARCH("b","Maine")>0,1,"") results in #VALUE!

    I have tried many variations of =IF(SEARCH("b","Maine")="VALUE!","",1) but cannot find a way to replace the #VALUE! with a zero or a blank.

    Suggestions please?

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

    Re: #VALUE! to be replaced (97)

    Try the ISERROR function:

    =IF(ISERROR(SEARCH("b","Maine")),"",1)

  3. #3
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #VALUE! to be replaced (97)

    Thanks AGAIN Hans.
    You just helped me out with Access problem last week. You seem to be everywhere!

    Once again, the obvious is the correct solution.
    And when you give the solution I want to hit myself on the head with a shovel because the solutions have been so simple once you pointed them out.
    In this case, I could not accept the concept of an error, because the function returned the value it said it would. Aaargh!

    Thank you again, your solution was perfect.

  4. #4
    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: #VALUE! to be replaced (97)

    If you are searching for the presence of the contents of B1 in A1 and want to return one if found and zero if not, you can also use:
    <code>=COUNTIF(A1,"*"&B1&"*")</code>
    If B1 might be empty, you would need to check for that - something like: <code>=IF(B1<>"",COUNTIF(A1,"*"&B1&"*"),"")</code>
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    May 2006
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: #VALUE! to be replaced (97)

    Thanks rory.

    Another forgotten tool from the toolbox.
    A good suggestion

Posting Permissions

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