locked
SP 2007 - how to use the search function in calculated field RRS feed

  • Question

  • I've got the following formula for the calculated field Blackberry Quantity:

    =IF(Search("Blackberry",Line1Select)>0,Line1Qty,IF(Search("Blackberry",Line2Select)>0,Line2Qty,IF(Search("Blackberry",Line3Select)>0,Line3Qty,IF(Search("Blackberry",Line4Select)>0,Line4Qty))))

    &IF(Search("Blackberry",Line5Select)>0,Line5Qty,IF(Search("Blackberry",Line6Select)>0,Line6Qty,IF(Search("Blackberry",Line7Select)>0,Line7Qty,IF(Search("Blackberry",Line8Select)>0,Line8Qty,IF(Search("Blackberry",Line9Select)>0,Line9Qty,0)))))

    Which says basically, if any of the fields Line1Select--Line9Select contain the text "blackberry", then place the corresponding Quantiy into the field Blackberry Quantity.

    I just found out that search returns an error rather than a zero value if a substring is not contained as part of a string.  Ugh.  Can somebody show me how to adapt this formula to take into account the error condition for each unsuccessful search?


    js



    • Edited by Geek 2016 Tuesday, July 2, 2013 4:13 PM
    Tuesday, July 2, 2013 4:10 PM

Answers

  • Hi tester2008,

    i think, you syntax have some issues, usually the syntax is using excel syntax.

    for search syntax the value will return 1 as found, and #value!error as not found. so if you put '>' as conditional, seems will not be correct, as it return only those 2.

    now the question is how to get the #value! when it came out, it turn out have a procedure called iserror().

    so basically, you can add this before your search,

    for example
    =if(iserror(search("a",a1)),"no good","good")


    Regards,
    Aries
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, July 3, 2013 4:33 AM

All replies

  • Hi tester2008,

    Thank you for your question. I am trying to involve someone familiar with this topic to further look at this issue. There might be some time delay. Appreciate your patience. Thank you for your understanding and support.

    Thanks,

    Entan Ming

    TechNet Subscriber Support in forum

    If you have any feedback on our support, please contacttnmff@microsoft.com.


    Entan Ming
    TechNet Community Support

    Wednesday, July 3, 2013 3:15 AM
    Moderator
  • Hi tester2008,

    i think, you syntax have some issues, usually the syntax is using excel syntax.

    for search syntax the value will return 1 as found, and #value!error as not found. so if you put '>' as conditional, seems will not be correct, as it return only those 2.

    now the question is how to get the #value! when it came out, it turn out have a procedure called iserror().

    so basically, you can add this before your search,

    for example
    =if(iserror(search("a",a1)),"no good","good")


    Regards,
    Aries
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Wednesday, July 3, 2013 4:33 AM