View original (French)

If cell part contains a word from a list

Solved
Coco -  
 Coco -
Hello to all Excel enthusiasts, this magical tool,

I have an existential question that I've been stuck on for a few hours, without finding a satisfactory answer on the Internet.

Let me explain:
In cell B1, I have a long text. I want to know if this text contains any of the words listed in the range D1:D10.

For example, if B1 contains "Hello, my name is Coco, I came to discuss my problem with excel"
and in D1:D10 I have "move", "belt", "translate", "basket", "narrow", "bludgeon", "problem", "verb", ...
This should return TRUE;

whereas if in D1:D10 I have "move", "belt", "translate", "basket", "narrow", "bludgeon", "razor", "verb", ...
This should return FALSE

Another requirement: the formula should be placed in C1, so that it can be copied down to check the condition for B2, B3, ...

How do I do that?
Is that clear?

2 answers

  1.
    via55 Posted messages 14395 Registration date   Status Member Last intervention   2 759
     
    Hello

    One possibility:
    In E1, enter the formula:
    =COUNTIF($B$1,"*"&D1&"*")
    and drag it down to E10
    When the word exists, the formula returns 1 (or 2 if the word is present twice), 0 otherwise

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
    1. Coco
       
      Thank you for your prompt response.

      However, the idea would be to get a unique formula for B1, which could be copied down for B2, B3... without having to create a new column to the right of D for each B cell.
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      Hello
      see here if this might correspond to what you're looking for
      https://mon-partage.fr/f/CNNYx8Vd/
      regards
      0
    3. Coco
       
      Thank you Vaucluse, it seems to be working pretty well. Ideally, it should be transferable to multiple files and intended for use by people who may not necessarily know how to create functions, so even if it works perfectly, it's still not the perfect gem I was hoping for :/

      In any case, thank you very much for taking the time, and it will probably be useful for future VBA projects anyway :D
      0
  2. via55 Posted messages 14395 Registration date   Status Member Last intervention   2 759
     
    Re,
    friendly greeting Vaucluse ☺,

    Or with a custom function
    https://mon-partage.fr/f/LdxrkXKY/

    Cheers
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    0
    1. Coco
       
      Perfect via55, thank you very much!

      =SUMPRODUCT((COUNTIF(A2,"*"&$F$2:$F$20&"*")))

      Wonderful, that's exactly what I needed, I hadn't thought of inserting a sumproduct to consider the entire range!
      I'm overjoyed, thank you :D
      0
    2. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
       
      And can you tell us which solution you ended up using?
      Looking forward to hearing from you.
      0
    3. Coco
       
      The sumproduct countif, coupled with two equivalents because the start and end rows of the range also depended on an amount located in another column, is a bit barbaric but it works perfectly :)
      0