How to find more than one word in Excel?

[man]

Hello

For example, I want to find cells that contains these 2 words [disposable] [bottles] in any order or with any other words in between, the search result shown will be A4 cell and A5 cell. How to do it? (Unable to use Ctrl+F because Ctrl+F only can search for 1 word)

My A1 cell to A6 cell contents are below
A1 cell: A water bottle is a container that is used to hold liquids, mainly water, for the purpose of transporting a drink while travelling or while otherwise away from a supply of potable water.
A2 cell: Water bottles are usually made of plastic, glass, metal, or some combination of those substances.
A3 cell: in the past, water bottles were sometimes made of wood, bark, or animal skins such as leather, hide and sheepskin.
A4 cell: Water bottles can be either disposable or reusable.
A5 cell: Disposable water bottles are often sold filled with potable water, while reusable bottles are often sold empty.
A6 cell: Reusable water bottles help cut down on consumer plastic waste and carbon emissions.

I am using Excel 2021

Thanks

 

[hrayani]

Hi, see below
is this what you want ??

Book1
	A	B
1	A water bottle is a container that is used to hold liquids, mainly water, for the purpose of transporting a drink while travelling or while otherwise away from a supply of potable water	Not Found
2	Water bottles are usually made of plastic, glass, metal, or some combination of those substances.	Not Found
3	A3 cell: in the past, water bottles were sometimes made of wood, bark, or animal skins such as leather, hide and sheepskin.	Not Found
4	A4 cell: Water bottless can be either disposable or reusable.	Found
5	A5 cell: Disposable water bottles are often sold filled with potable water, while reusable bottles are often sold empty.	Found
6	A6 cell: Reusable water bottles help cut down on consumer plastic waste and carbon emissions.	Not Found
Sheet1

Cell Formulas
Range	Formula
B1:B6	B1	=IF(OR(AND(ISNUMBER(SEARCH("disposable", A1)), ISNUMBER(SEARCH("bottles", A1))), AND(ISNUMBER(SEARCH("bottles", A1)), ISNUMBER(SEARCH("disposable", A1)))), "Found", "Not Found")

 

[Cubist]

Book1
	A	B
1	A water bottle is a container that is used to hold liquids, mainly water, for the purpose of transporting a drink while travelling or while otherwise away from a supply of potable water	Not Found
2	Water bottles are usually made of plastic, glass, metal, or some combination of those substances.	Not Found
3	A3 cell: in the past, water bottles were sometimes made of wood, bark, or animal skins such as leather, hide and sheepskin.	Not Found
4	A4 cell: Water bottless can be either disposable or reusable.	Found
5	A5 cell: Disposable water bottles are often sold filled with potable water, while reusable bottles are often sold empty.	Found
6	A6 cell: Reusable water bottles help cut down on consumer plastic waste and carbon emissions.	Not Found
Sheet5

Cell Formulas
Range	Formula
B1:B6	B1	=IF(ISNUMBER(SEARCH("Bottle",A1))*ISNUMBER(SEARCH("Disposable",A1)),"Found","Not Found")

 

[man]

Thank you for all replies. How do you use the Cell Formulas that you posted? I am not sure how to apply your answer to see the result.

I mean if I press Ctrl+F key, [Find what:] box if i input the text [disposable] then click [Find all] or [Find next] it will show me the cell that contain the word [disposable], if I click [Find Next] it will show me the next cell that contain the word [disposable]. The Ctrl+F feature is unable to search for cells that contains these 2 words [disposable] [bottles] in any order or with any other words in between, so I am asking my question here. Thanks.

 

[Cubist]

Copy the formula. Paste it cell B1, and drag it down. It will return "Found" or "Not found"

 

[man]

Thank you for your answer.

Two more questions, please provide me the formula:
Find cells that contains these 3 words [disposable] [bottles] [water] in any order or with any other words in between.
Find cells that contains these 4 words [disposable] [bottles] [water] [sold] in any order or with any other words in between.

 

[man]

If my data is not only in column A, data is in many columns and cells, how can I find all cells in the excel sheet to find cells that contains these 2 words [disposable] [bottles] in any order or with any other words in between?
Thanks

 

[Cubist]

Try this new formula. You can add or remove words as you like. In this example, you're looking for Bottle, Disposable, and Sold so the number to the right of the equal sign is 3. You can add in the 4th word and change it to 4 accordingly.

=IF(SUM(COUNTIF(A1,"*"&{"Bottle","Disposable","Sold"}&"*"))=3,"Found","Not Found")

If your data in many cells in a tabular structure, consider using conditional formatting to highlight the cells.
Select all the cells you want to perform the search on -> Home tab -> Conditional formatting -> New Rule -> Custom/Use formula to determine which cell to format. Paste this formula in. Again, you can remove or add words as needed, remember to adjust the number as well.

=SUM(COUNTIF(A1,"*"&{"Bottle","Disposable","Sold"}&"*"))=3

 

[man]

Try this new formula. You can add or remove words as you like. In this example, you're looking for Bottle, Disposable, and Sold so the number to the right of the equal sign is 3. You can add in the 4th word and change it to 4 accordingly.

=IF(SUM(COUNTIF(A1,"*"&{"Bottle","Disposable","Sold"}&"*"))=3,"Found","Not Found")

If your data in many cells in a tabular structure, consider using conditional formatting to highlight the cells.
Select all the cells you want to perform the search on -> Home tab -> Conditional formatting -> New Rule -> Custom/Use formula to determine which cell to format. Paste this formula in. Again, you can remove or add words as needed, remember to adjust the number as well.

=SUM(COUNTIF(A1,"*"&{"Bottle","Disposable","Sold"}&"*"))=3

First formula works.
Second formula after I click OK it shows me a message "You may not use reference operators (such as unions, intersections, and ranges), array constants, or the LAMBDA function for Conditional Formatting criteria." (screenshot below)

 

[Cubist]

My mistake. For conditional formatting, we'd go back to the original format. Notice the structure pattern below. You can add/remove words accordingly. Make sure the cell reference "A1" is whatever the top left cell of your selection is.

=ISNUMBER(SEARCH("Bottle",A1))*ISNUMBER(SEARCH("Disposable",A1))*ISNUMBER(SEARCH("Sold",A1))