# Thread: Find Profanity in Bulk with Formula Thanks: 0 Likes: 0

1. ## Find Profanity in Bulk with Formula

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.

2. ## Re: Find Profanity in Bulk with Formula

Hi, how about something like this.

Excel 2013/2016
ABCD
1list of namescontains profanity?Profane words
2feck text text textTRUEfeck
3no profrane wordsFALSEduck
4text duck textTRUEbanker
5text text bankerTRUE
6text no profanityFALSE

Sheet1

Worksheet Formulas
CellFormula
B2=ISNUMBER(LOOKUP(1,-SEARCH(\$D\$2:\$D\$4,A2)))

3. ## Re: Find Profanity in Bulk with Formula

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.

4. ## Re: Find Profanity in Bulk with Formula

Originally Posted by DWightman
.. 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?
Originally Posted by DWightman
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.

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•