Search for non-consecutive combination of words in Excel.

D

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.
 
B

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Searching for non-consecutive text 3
Specific words Categorizaion 1
In Excel, print non-consecutive columns. 1
Search OF Macros for a specific phrase 2
Word Macro for multiple searches, incl. in footnotes 2
Search, find and highligh multple words in one document 0
Conditional formatting with multiple, non-consecutive values?? 8
How to Reverse the Order of Specific Consecutive Words 3

Top