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

 

[man]

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))

I use your formula

then after I click OK the [New Formatting Rule] box disappears and I see the cell contents only

How to see the results that tell me what cells contain Bottle, Disposable, and Sold?

 

[Cubist]

Look at the Preview. You have not set a Format. Click on Format... You can choose to highlight, strikeout, font color ,ect...

 

[man]

View attachment 110263
Look at the Preview. You have not set a Format. Click on Format... You can choose to highlight, strikeout, font color ,ect...

It works. I click Format>Font style [Italic]>OK>OK, then I see the cell that contain Bottle, Disposable, and Sold changed to italic.

How to undo the format changes? After I see the cells data so do I click undo to undo the format changes?
If I have thousands of rows of does it mean to see all the results I have to scroll thousands of rows to see all the cells are italic?
What is "highlight"? I do not see any highlight feature in Format Cells>Font tab

 

[Cubist]

Anything that was done by conditional formatting can only be undone by removing the rules. The highlighting is under "Fill".

 

[man]

Anything that was done by conditional formatting can only be undone by removing the rules. The highlighting is under "Fill".

What are the steps to removing the rules?

 

[Cubist]

Conditional formatting -> Clear rules.

Which version of Excel are you using? It's possible to extract the text that only contains those words if you're using 365.

 

[man]

I use Excel 2021

 

[Cubist]

Version 2021 doesn't have the dynamic functions that would make things easy. I can offer a VBA option.
If you do want VBA, would you like to
(1) Italicize/highlight the text and give you the option to undo it OR
(2) extract all the texts to say Sheet2?

 

[man]

Version 2021 doesn't have the dynamic functions that would make things easy. I can offer a VBA option.
If you do want VBA, would you like to
(1) Italicize/highlight the text and give you the option to undo it OR
(2) extract all the texts to say Sheet2?

I would like to extract all the texts to Sheet2

 

[man]

If extract all the texts to Sheet2, does it mean the original sheet data is unchanged? All the cells that matches the Find criteria will be copied to Sheet2?