Results 1 to 4 of 4

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

  1. #1
    New Member
    Join Date
    Oct 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,092
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default 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)))

    [code]your code[/code]

  3. #3
    New Member
    Join Date
    Oct 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,092
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Find Profanity in Bulk with Formula

    Quote Originally Posted by DWightman View Post
    .. 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?
    Quote Originally Posted by DWightman View Post
    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.
    Last edited by FormR; Jun 28th, 2019 at 03:14 AM.
    [code]your code[/code]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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