How to Check a Cell for Multiple Text Strings

Question

I am trying to find a way to search a list (“Search Terms” in screenshot) and see if that text string contains both word 1 (D2) and word 2 (E2). The order in which “word 1” or “word 2” in the search doesn’t matter. I would like to know if they are both present in the “search term”.

I found this formula: =SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0

This very close to what I need. However, it designed to search 1 list.

Is there a way to create an "and" function in the formula above? So that the formula searches both “word 1" and “word 2" lists and returns true if both words are present in the keyword. Returns a false value if 1 or zero of the 2 words is present in the search term.

Check Cell of Many Things

Hello, I am wanting to search 2,000+ cells to see if the contain "Word 1" and "Word 2". "Word 1" and "Word 2" can be any where in the searched text string. I want to know if both are present in that cell. Attached is a screenshot of the data I am working with. Thanks for your reply and let me know if you have more questions. Thanks! I was going to add a screenshot but I don't seem to be able to do so. — mrick
– mrick, Commented Oct 28, 2019 at 15:08
Edit your question, instead of adding the detail in a comment, thanks! I can add a screenshot, if you just provide the link. — BigBen
– BigBen, Commented Oct 28, 2019 at 15:09
What version of Excel are you using? Are you using Excel 2013 or later versions? Please provide some sample data and sample key words and specify how many key words you are wanting to search within each string? — Terry W
– Terry W, Commented Oct 28, 2019 at 21:51

BigBen · Accepted Answer · 2019-10-25 16:33:22Z

1

Perhaps like this:

=SUMPRODUCT(--ISNUMBER(SEARCH({"garden","watering"},B5)))=2

If you want to add more criteria or quickly modify the criteria, perhaps use a list, like so:

=SUMPRODUCT(--ISNUMBER(SEARCH($C$1:$C$3,B5)))=COUNTA($C$1:$C3)

edited Oct 25, 2019 at 16:33

answered Oct 25, 2019 at 16:27

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

mrick Over a year ago

Hello BigBen, The list idea is great. This is very close to what I am looking for. What if I had 2 lists to pull from and need to see if both words were contained in the text cells. For example:

BigBen Over a year ago

You can edit your question perhaps with the additional detail.

mrick Over a year ago

Hello BigBen, The list idea is great. This is very close to what I am looking for. What if I had 2 lists to pull from and need to see if both words were contained in the text cells. Let's say I have a second list to look in Column D. It contains "help", "plants", "dirt". I need to see if the text cells in column B contain "garden" and "help". The words can be in any order in Column B. I just need to know if they are present. Then I would like to check for "watering" and "plants", etc... I will be searching 1000's of text cells with 100s of word combinations. Thanks for you help!

BigBen Over a year ago

Please - edit your original question (click the link). That is the best way for you to help me help you :)

Terry W Over a year ago

@mrick you should update your question with some sample strings, sample key words, and demonstrate how the expected outcome should look like. You can ask follow up questions but if it is out of the scale of your original question you should start a new question while letting people who answered your question if their solutions are working or not for you. If not specify where went wrong.

Collectives™ on Stack Overflow

How to Check a Cell for Multiple Text Strings

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related