CODEWITHSUNDEEP
excelsearchexcel-formulacontainsconditional-formatting
WLO
WLO

Reputation: 3

Identify cells containing a set of strings

I have about 18,000 rows of different item descriptions. Let's say an item is called like this: Elbow 45, Steel, Swivel, 4" but it is not always written in this order. Sometimes the same part is written in a different order like this:

Elbow, 45, Swivel, 4", Steel
or
Steel, 45, Elbow, Swivel, 4"

If I try to find it with Crtl+f, it does not work because I have to type in exactly what a cell contains in the exact order.

Can I also search for example: Find me all cells which contain "Elbow" and "Swivel" and "Steel" and so on and it will show me all cells?

Upvotes: 0

Views: 148

Answers (1)

pnuts
pnuts

Reputation: 59475

Add parameters (say Steel to E1, Elbow to E2 and Swivel to E3) and select the column (assumed to be ColumnA), HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format, Format values where this formula is true:: 

=AND(SEARCH($E$1,A1)>0,SEARCH($E$2,A1)>0,SEARCH($E$3,A1)>0)

and select a fill colour.

Upvotes: 2

Related Questions