View original (French)

Excel IF Formula Cell Contains Words

Solved
FormulaSeeker Posted messages 13 Status Membre -  
 Duncan -
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 réponses

Réponse 1 / 4
via55 Posted messages 14730 Registration date   Status Membre Last intervention   2 755
 
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
FormulaSeeker Posted messages 13 Status Membre
 
Hello,

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

Have a great day!
0
Vaucluse Posted messages 27336 Registration date   Status Contributeur Last intervention   6 453
 
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