×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

MS Excel 2013 SEARCH Function question

MS Excel 2013 SEARCH Function question

MS Excel 2013 SEARCH Function question

(OP)
I am using a SEARCH function like this: =IF(ISNUMBER(SEARCH("LOS",$A6))=TRUE,$C6,"")

But, what I really want to do is to SEARCH for "LOS" OR "DTS" in the referenced cell. Can anyone show me how to format this?

Thanks,

John

RE: MS Excel 2013 SEARCH Function question

Your cell will contain either "LOS" or "DTS", but never ever anything else?

You cell might contains things like "randomDTStext" or "unimportantLOSpart"?

Something else?

RE: MS Excel 2013 SEARCH Function question

Not sure of the syntax, but I think it would look something like this.

=IF(OR(ISNUMBER(SEARCH("LOS",$A6)),ISNUMBER(SEARCH("DTS",$A6)))=TRUE,$C6,"")

RE: MS Excel 2013 SEARCH Function question

COUNTIF is simpler:
=IF(COUNTIF(A6,"*LOS*")+COUNTIF(A6,"*DTS*")>0,C6,"")

combo

RE: MS Excel 2013 SEARCH Function question

Interesting solution!!

RE: MS Excel 2013 SEARCH Function question

(OP)
I thank you all for your responses. Another answer was staring me in the face. So, since I was using an IF statement I did this: =IF(ISNUMBER(SEARCH("Desktop Simulator",$J6))=TRUE,$K6,(IF(ISNUMBER(SEARCH("LOS",$J6))=TRUE,$K6,""))). It may be a little clunky but it works. I will be keeping your suggestions on file for future projects.

mintjulep the two terms are unique and they will be in a string of text.

Does anyone see a problem with my solution? Please let me know.

Thanks,

RE: MS Excel 2013 SEARCH Function question

It works, but the formula size grows significantly when you add new conditions. Moreover, it is hard to debug. ISNUMBER returns TRUE/FALSE, no need to add "=TRUE".
Presonally I would combine SEARCH results in single OR formula, without nested IFs, esp. due to common output ($K6 or ""):
=IF(OR(ISNUMBER(SEARCH("Desktop Simulator",$J6)),ISNUMBER(SEARCH("LOS",$J6))),$K6,"")
New condition is simple in this case:
=IF(OR(ISNUMBER(SEARCH("Desktop Simulator",$J6)),ISNUMBER(SEARCH("LOS",$J6)),ISNUMBER(SEARCH("DTS",$J6))),$K6,"")

combo

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close