Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts

    Testing error conditions?

    I have a simple formula which copies text up to an underscore from one column to another. It's "=LEFT(D36,SEARCH("_",D36)-1)" (cell D36 contains ABCDEF_1234 or something similar.

    If the text in cell D36 does not contain an underscore. The formula returns "#VALUE!" (no quotes). But I don't seem to be able to test for it. So for example if I put in "=IF(SEARCH("_",D36)="#VALUE!","Error", "OK"), the answer comes back "#VALUE!" if there is no underscore.

    So, how can I test for an error condition so I can display something else in that cell and not "#VALUE!"?

    Thanks for any advice

    Alan

  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(iserror(SEARCH("_",D36)),"Error", "OK")

    What you were testing is for the literal text string "#VALUE" and the cell does not contain text.

    Steve

  3. The Following User Says Thank You to sdckapr For This Useful Post:

    alan sh (2012-08-28)

  4. #3
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Quote Originally Posted by sdckapr View Post
    =IF(iserror(SEARCH("_",D36)),"Error", "OK")

    What you were testing is for the literal text string "#VALUE" and the cell does not contain text.

    Steve
    Thank you. I've learned something new today

    Alan

  5. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    In Excel 2010, there's an IFERROR function that might be useful to you:

    =IFERROR(LEFT(D36,SEARCH("_",D36)-1),"Error")

  6. The Following User Says Thank You to kweaver For This Useful Post:

    alan sh (2012-08-28)

  7. #5
    3 Star Lounger
    Join Date
    Apr 2007
    Location
    Lancashire, United Kingdom
    Posts
    264
    Thanks
    32
    Thanked 5 Times in 5 Posts
    Thanks

    Alan

Posting Permissions

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