Find Profanity in Bulk with Formula

[DWightman]

Excel Experts,

I have a customer who has given me a spreadsheet of about 1,300 words they consider profane. They will be sending me a mailing list of names they want to send samples to. However, they do not want to send a sample to any customer name that contains one of the words they consider profane.

I know I could do this with a find function over and over again, 1,300 times. I know I can do this with the filter and contains function, but again one by one and 1,300 times. I don't think a v-lookup would work for this because I'm not going to have a field between the two files that is an exact match. I also think if I use the TRUE option of the v-lookup it still won't get me what I would be looking for.

Is there a way to, or does anyone know of a way using a formula or some other way in bulk to find all the names that may contain a profane value?

I hope this is enough of a description to start a conversation or get me some help. I can post more details if needed.

Thanks.

 

[FormR]

Hi, how about something like this.

Excel 2013/2016
	A	B	C	D
1	list of names	contains profanity?		Profane words
2	feck text text text	TRUE		feck
3	no profrane words	FALSE		duck
4	text duck text	TRUE		banker
5	text text banker	TRUE
6	text no profanity	FALSE
Sheet1

Cell Formulas
Range	Formula
B2	=ISNUMBER(LOOKUP(1,-SEARCH($D$2:$D$4,A2)))

 

[DWightman]

FormR

Thank you for the help. I do believe what you have listed will work for what I need. If I use the wizard and build the ISNUMBER formula like you show.... I keep my list of profane words in a different spreadsheet and then insert a column into my mail file and put that formula in it? Or how do I use what you have graciously provided above? Put my list of profane words in the same spreadsheet on a different tab? I'm thinking I kind of know how to use what you did.

 

[FormR]

.. I keep my list of profane words in a different spreadsheet and then insert a column into my mail file and put that formula in it?

Put my list of profane words in the same spreadsheet on a different tab?

Hi, I don't see any reason why you couldn't do either.