Advanced search within a spreadsheet

[Benwyatt1]

Hi guys, I wonder if you can help with an issue I have,

I have a large excel spreadsheet (with over 30,000 rows). One of the columns has long strings of text.

I need to search the spreadsheet for certain words. However if I just use the find function I can only search for one word at a time. What I want to be able to do is, search the column of cells of text for all cell that contain for example the word "tree" AND "flower" OR "leaf" OR "petal".

So all results must find cells that have tree in the body of text and must also contain any of the words flower, leaf or petal.

Is if possible to make this kind of search in excel?

Thank you for your help

[oeldere]

Probably you need VBA for this.

In that case i can't help you enough with that.

I bet you get better help if you add an Excel file without confidentional information.

[martindwilson]

you could just use filter there are several ways including advanced but for simplicity say your data is in column a starting a2
in b2 put
=AND(ISNUMBER(SEARCH("tree",A2)),OR(ISNUMBER(SEARCH("flower",A2)),ISNUMBER(SEARCH("leaf",A2)),ISNUMBER(SEARCH("petal",A2))))
drag to end then filter column b on true
then select the filtered cells chose a fill colour and un filter all the found ones will be coloured
or if you don't want to hard set those words
=AND(ISNUMBER(SEARCH($I$2,A2)),OR(ISNUMBER(SEARCH($J$2,A2)),ISNUMBER(SEARCH($K$2,A2)),ISNUMBER(SEARCH($L$2,A2)))) wher I2 contains the word tree j2,k2,l2 contain the others

[Benwyatt1]

Thank you martindwilson that works perfectly.

Is there any way to extract just the rows that have returned 'TRUE' say into a separate sheet or will I just need to go through them manually? Not to worry if not you have all ready helped save me hours!

[Benwyatt1]

Thank you martindwilson that works perfectly.

Is there any way to extract just the rows that have returned 'TRUE' say into a separate sheet or will I just need to go through them manually? Not to worry if not you have all ready helped save me hours!

[martindwilson]

if ,when filtered, you copy the entire rows you can paste to another sheet