Filter words only not containing.

[LEFROGUE]

Hello,

i am wondering how to filter words in a data set. e.g men, male without it being part of other words (lament, female etc).

Is there a way of using advanced filter for this rather than a formula?

Thankyou

 

[LEFROGUE]

Hi sorry for not being clear.

So I have multiple data sets and I want to remove the data including certain keywords (on sheet) however, some of the words overlap into other words (men -> menopause) which i do not want to delete.

How do i filter out the certain words using a formula or advanced filter when they are apart of a phrase e.g 'male breast pain'

This mini sheet shows the cells i want to identify highlighted.

excle mr.xlsx
	G	H	I	J	K
3	Keyword		Keyword
4	Dogs		menstrual breast pain		male nipple pain
5	men		male nipple pain		mastitis on cow
6	male		mastitis pain		dogs has mastitis
7	Goat		mastitis		goat breast abcess
8	Cow		breast pain		male breast inflammed
9	Bovine		bovine mastitis		right breast pain male
10	Foxes		mastitis on cow		signs of mastitis for dogs
11	Cats		fibrocystic breast disease
12	Foxes		mastalgia
13			mastitis symptoms
14			tender breast meaning
15			dogs has mastitis
16			sternum pain
17			boobs sore after period
18			breast engorgement
19			pain under left breast
20			sharp pain in left breast that comes and goes
21			mastitis treatment
22			engorgement
23			goat breast abcess
24			breast pain before period
25			sharp pain in breast
26			pain in middle of chest bone
27			pain under breast bone above stomach
28			male breast inflammed
29			sore breast
30			pain between breasts in center of chest
31			sore boobs
32			left breast pain
33			breast pain causes
34			sharp pain under left breast
35			pain under my left breast and ribs
36			sharp pain in breast that comes and goes
37			right breast pain male
38			pain in upper stomach under breasts
39			sharp pain under left breast that comes and goes
40			sharp pain in right breast that comes and goes female
41			female chest pain left side above breast
42			pain between breasts in center of chest female
43			painful lump in breast
44			shooting pain in breast
45			granulomatous mastitis
46			mastitis meaning
47			right side chest pain above breast
48			breast pain reason
49			signs of mastitis for dogs
50			burning sensation in breast
51			sharp pain in right breast that comes and goes
52			nipple pain before period
Sheet1

 

[Peter_SSs]

Thanks.

I removed one of the "Foxes" from the left since you had that twice.

I did not have success using advanced filter with the data in formal tables but just as a normal ranges I did this

I put the formula shown in J4
Selected I3:I52
Invoked Advanced Filter as follows. Not the Criteria range must include the blank cell above the formula.

Result:

LEFROGUE.xlsm
	G	H	I	J	K	L
3	Keyword		Keyword			Keyword
4	Dogs		menstrual breast pain	0		male nipple pain
5	men		male nipple pain			bovine mastitis
6	male		mastitis pain			mastitis on cow
7	Goat		mastitis			dogs has mastitis
8	Cow		breast pain			goat breast abcess
9	Bovine		bovine mastitis			male breast inflammed
10	Foxes		mastitis on cow			right breast pain male
11	Cats		fibrocystic breast disease			signs of mastitis for dogs
12			mastalgia
13			mastitis symptoms
14			tender breast meaning
15			dogs has mastitis
16			sternum pain
17			boobs sore after period
18			breast engorgement
19			pain under left breast
20			sharp pain in left breast that comes and goes
21			mastitis treatment
22			engorgement
23			goat breast abcess
24			breast pain before period
25			sharp pain in breast
26			pain in middle of chest bone
27			pain under breast bone above stomach
28			male breast inflammed
29			sore breast
30			pain between breasts in center of chest
31			sore boobs
32			left breast pain
33			breast pain causes
34			sharp pain under left breast
35			pain under my left breast and ribs
36			sharp pain in breast that comes and goes
37			right breast pain male
38			pain in upper stomach under breasts
39			sharp pain under left breast that comes and goes
40			sharp pain in right breast that comes and goes female
41			female chest pain left side above breast
42			pain between breasts in center of chest female
43			painful lump in breast
44			shooting pain in breast
45			granulomatous mastitis
46			mastitis meaning
47			right side chest pain above breast
48			breast pain reason
49			signs of mastitis for dogs
50			burning sensation in breast
51			sharp pain in right breast that comes and goes
52			nipple pain before period
Sheet3

Cell Formulas
Range	Formula
J4	J4	=COUNT(SEARCH(" "&G$4:G$11&" "," "&I4&" "))

 

[LEFROGUE]

Hi Peter,

works a dream, going to just use the count search formula as in a table so need to delete rows.

thankyou very much for help.

 

[Peter_SSs]

You're welcome. Glad to help. Thanks for the follow-up.