Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for non-consecutive combination of words in Excel.
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Search for non-consecutive combination of words in Excel.
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 "Dr.Robert" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I search for words in all caps in Excel? | Excel Discussion (Misc queries) | |||
search of words | Excel Discussion (Misc queries) | |||
Search for repeat words in Excel database | Excel Discussion (Misc queries) | |||
Search for specific words | Excel Worksheet Functions | |||
How can I search for words in all caps in Excel? | Excel Discussion (Misc queries) |