Search for non-consecutive combination of words in Excel.

[Dr.Robert]

EX: I have a spreadsheet of body anatomy and physiology. I want to find every
cell (or row) in my data that contains the non-consecutive words brain AND
emotion, for instance.
Excel search will find one or the other, but not both words.
It could be done in several steps - ie -
Step1 - find all instances of 'brain' and copy them to new worksheet
Step2 - find all instances of 'emotion' in the secondary worksheet.
I don't have much experience with macros, though I have done a bit in Word.

 

[Bernie Deitrick]

Dr. Robert,

No need for macros. Insert a new column (Let's say, a new column A) and two new rows at the top of
your sheet. In A1 enter brain and in A2, enter emotion

In A, enter the "Show", and in A4, enter formula

=AND(COUNTIF(B4:IV4,"*" & $A$1 &"*")>=1,COUNTIF(B4:IV4,"*" & $A$2 &"*")>=1)

and copy down to match all your data. Then Select from A3 to the bottom of column A, select Data /
Filter Autofilter, and then choose TRUE from the dropdown on Cell A3

HTH,
Bernie
MS Excel MVP