View original (French)

Excel IF Formula Cell Contains Words

Solved
FormulaSeeker -  
Hello,

I am desperately searching for the right formula that would allow me to categorize over a thousand stores:

In A1 I have, for example:
Store XXX type Super Address XXX
Store XXX type Hyper Address XXX
Store XXX Type Express Address XXX
Store XXX Type Marché Address XXX
Store XXX Type Utile Address XXX

So I have 5 different categories of stores. I want cell B1 to display only the category type Super, Hyper, Express, etc.

Knowing that any cell containing "Utile" will necessarily contain "Express", but not the other way around.

Here is the formula I'm trying:

=IF(COUNTIF(D2,"*Hyper U*"),"Hyper U",IF(D2,"*Super U*","Super U",IF(D2,"*Marché*","Marché U",IF(D2,"*Utile*","Utile","U Express")))))

This does not work and Excel indicates that I have entered too many characters... Is there a possibility for a formula to handle 5 conditions?

Thank you very much for your helpful assistance!

4 answers

  1.  via55
    Good evening

    You can of course nest more than 5 If statements, but the problem with your formula is that the COUNTIF is unnecessary; it should be:
    =IF(D2,"*Hyper U*","Hyper U",IF(D2,"*Super U*","Super..etc

    Best regards
    Via

    --
    "Imagination is more important than knowledge." A. Einstein
    3
    1. FormulaSeeker Author
      Hello,

      Thank you very much for taking the time to respond to me!

      Have a great day!
      0
    2. Vaucluse Contributor
      Hi Via, how are you?
      Are you sure your formula works with an IF code that doesn't use any operator? If so, let me know how, I'm interested!
      Cheers
      0