Question How to find many words in Microsoft Excel 2021 ?

[smalltech]

Hello,

There are many Excel cells with many text and I want to search for the cell that contains multiple keywords in no particular order, how to search?

For example the excel sheet has A1 to A6 total 6 cells contents below.

I want find the cell that contains these 2 words "reusable" and "plastic", the result shown to me will be A6 cell.

I want find the cell that contains these 3 words "water" and "wood" and "leather", the result shown to me will be A3 cell.

How to search/find in Excel 2021?

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.

Thanks

 

[Ralston18]

Appears to be a homework or test question. Per Forum rules we cannot do that for you.

= == =

Update your post to show what you have tried thus far and the resulting successes or failures.

Excel has some very power search capabilities; both directly and indirectly via some logical functions.

As a bit of a hint look into text functions, strings, parsing. Also IF, THEN, ELSE.

Then post again showing your work.

Ask specific questions and include what you believe to be the answer or problem. Provide references as necessary.

 

[USAFRet]

What is the use case for this function?

I feel this could be better done in a word processor or database tool.

 

[smalltech]

Appears to be a homework or test question. Per Forum rules we cannot do that for you.

= == =

Update your post to show what you have tried thus far and the resulting successes or failures.

Excel has some very power search capabilities; both directly and indirectly via some logical functions.

As a bit of a hint look into text functions, strings, parsing. Also IF, THEN, ELSE.

Then post again showing your work.

Ask specific questions and include what you believe to be the answer or problem. Provide references as necessary.

Not homework or test question. I am just asking how to find words in excel. It is just a find feature that exist in all software, browser etc...

All the cells are just examples.

I usually use Ctrl+F and the [Find and Replace] will popup, Ctrl+F can only find a single word e.g. [water] in any cell or multiple words in a row e.g. [Water bottles can be either disposable or reusable], I am unable to search for cells that contain these 2 words "disposable" and "bottles", A4 cell and A5 cell will not be shown in the result.

For example, I want to search for 2 words "disposable" and "bottles" and see A4 cell and A5 cell in my result.
A4 cell contents is: 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.

In Ctrl+F feature, there is no way to input [disposable] and [bottles] or [disposable bottles] or [disposable/bottles] to show A4 cell and A5 cell as the result, I just want to tell excel please show me the cells that contain these 2 words [disposable] [bottles] (in any order and they need not be consecutive words and can have other words in between)?

I guess this is not some formula or whatever complicated stuff for someone who has more knowledge for excel. Maybe there are some advanced settings in Ctrl+F or somewhere that can do what I want.

 

[smalltech]

What is the use case for this function?

I feel this could be better done in a word processor or database tool.

I open an excel file and I want to find cells that contain these 2 words, e.g. [disposable] [bottles] in any order and they need not be consecutive words and can have other words in between.in the excel document. The data is already in excel.

 

[USAFRet]

I open an excel file and I want to find text in the excel document. The data is already in excel.

I get that you want to find the data in the Excel file.

My question was really....Why Excel?

 

[smalltech]

I get that you want to find the data in the Excel file.

My question was really....Why Excel?

The data was recorded in Excel in the past. What is wrong with excel that has data inside?

Just curious if it is not excel, if this line "Water bottles can be either disposable or reusable." was in Microsoft Word or Notepad, in Ctrl+F feature, is it possible to input [disposable] and [bottles] or [disposable bottles] or [disposable/bottles] to tell Microsoft Word or Notepad please show me the line that contain these 2 words [disposable] [bottles] (in any order and they need not be consecutive words and can have other words in between)?

 

[USAFRet]

The data was recorded in Excel in the past. What is wrong with excel that has data inside?

Just curious if it is not excel, if this line "Water bottles can be either disposable or reusable." was in Microsoft Word or Notepad, in Ctrl+F feature, is it possible to input [disposable] and [bottles] or [disposable bottles] or [disposable/bottles] to tell Microsoft Word or Notepad please show me the line that contain these 2 words [disposable] [bottles] (in any order and they need not be consecutive words and can have other words in between)?

(not notepad)

It can be done in Excel.
But, Excel is a great calculator. It is a lousy word processor. It is a lousy database.

But for Excel, look into the IF and ANDIF functions.


Again, what is the basis for this?
Where might the output go?
How much data are you working with?

 

[Ralston18]

Here is a tutorial link that provides lots of information and explaination with respect to searching for text in Excel cells.

Premise being that you are "stuck" with Excel.

https://www.ablebits.com/office-addins-blog/excel-if-cell-contains-formulas/

Your project will require two basic actions:

1) Find all the cells that contain a specific word.

2) Create the logic that will allow you find all the cells that contain a specific word and also another specific word.

Start with 1) above and once that is working apply what you have learned and incorporate that learning into the logic.

If there are problems then take screenshots of your spreadsheet and work that show formulas etc..

Post the screenshots here via imgur (www.imgur.com).

There are multiple ways to do what you require - just start with what seems most applicable to your requirements and current knowledge.

 

[smalltech]

I think you guys are over thinking about problem. My question is very simple, for example I want to search for 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? (maybe there are some settings/plugins or whatever I do not know of that is why I am asking here)

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.

 

[smalltech]

Again, what is the basis for this?
Where might the output go?
How much data are you working with?

I have no idea what is "basis", "output" meaning. The data is about a few thousand rows.

 

[smalltech]

Here is a tutorial link that provides lots of information and explaination with respect to searching for text in Excel cells.

Premise being that you are "stuck" with Excel.

https://www.ablebits.com/office-addins-blog/excel-if-cell-contains-formulas/

Your project will require two basic actions:

1) Find all the cells that contain a specific word.

2) Create the logic that will allow you find all the cells that contain a specific word and also another specific word.

Start with 1) above and once that is working apply what you have learned and incorporate that learning into the logic.

If there are problems then take screenshots of your spreadsheet and work that show formulas etc..

Post the screenshots here via imgur (www.imgur.com).

There are multiple ways to do what you require - just start with what seems most applicable to your requirements and current knowledge.

I do not understand the link you posted. Do you know what info should I use from that link for my problem?

 

[USAFRet]

I have no idea what is "basis", "output" meaning. The data is about a few thousand rows.

You have an excel file, with a bunch of text in different cells.

You wish to find some words, or combinations of words in these cells.

Why?
What might you do with that result?

 

[smalltech]

You have an excel file, with a bunch of text in different cells.

You wish to find some words, or combinations of words in these cells.

Why?
What might you do with that result?

I just want to find that cell and read the contents that was written inside that cell.

For example in the past I wrote in an excel cell "John knows how to play the guitar, piano, violin and drum". Now I want to find someone that knows how to play the drum and guitar, so I will search for 2 words [drum] [guitar] and this cell will appear then I can read the info to know John knows how to play the drum and guitar.

 

[USAFRet]

IN the above linked tutorial, the part you're looking for is:
"If cell contains several strings (AND logic)"


How would *I* do it?
With MS Access.

Link that Excel file in Access.
Access then treats it as an internal table.
Create a Find query, looking for the words "drum" AND "guitar".

That would return only those rows with both of those values.

Leverage the SQL functionality of a database tool, instead of trying to forcefeed a calculator to do this.

 

[Ralston18]

This:

"I do not understand the link you posted. Do you know what info should I use from that link for my problem?"

Yes.

But what did you try? What functions? What happened when you tried?

Just create a copy of the spreadsheet and starting working on the copy. Especially if thousands of rows.

Delete all but a couple of hundred rows to speed things up.

Be sure that the original spreadsheet is left alone and backed up somewhere away from your computer.

= = = =

That being said - if this is a real world problem then the data (as has been mentioned) should be migrated into a database that is far more readily searchable than Excel.

What you are now trying to do in Excel is generally straightforward but can be, and is, cumbersome.

Other more advanced search criteria and requirements would likely be much more difficult.