Excel IF Formula Cell Contains Words Solved

Question

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!

Mike-31 · Accepted Answer

Good evening,

if in column A you have for example "Store XXX type Super Address XXX"
with simply Super
in your formula, do not refer to Super U IF(D2;"*Super U*";"Super U" ;

then you write that your values are in column A and your formula refers to column D

so if your values are indeed in column A starting from A1, your first formula will be
=IF(COUNTIF(A1;"*Super*")=1;"Super";IF(COUNTIF(A1;"*Hyper*")=1;"Hyper";IF(COUNTIF(A1;"*Express*")=1;"Express";IF(COUNTIF(A1;"*Market*")=1;"Market";IF(COUNTIF(A1;"*Useful*")=1;"Useful";"")))))
--
Best,
Mike-31

I am responsible for what I say, not for what you understand...

via55 · Answer

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

Vaucluse · Answer

Good evening
please see here if it suits you and come back if needed:
https://mon-partage.fr/f/W3B2Nwgd/

note: no more than one word searched per text
adapt the reference list and its address in the formula to your needs
regards
The quality of the response mainly depends on the clarity of the question, thank you!

Duncan · Answer

Hello,

In sheet 1: A1, I have a dropdown list of names.
In sheet 2: I have the list of names in A, with their Employee ID in B.
I would like B1 to display the Employee ID of the name selected in A1.

Thank you.

Excel IF Formula Cell Contains Words

4 answers

Texture issues in enshrouded

Print a page in the right orientation under windows 11

Hyperlink

Motorola frp locked

Convert ts to m2ts

Display issue – restart required

Potentiometer not working

I can't even find a single string.

Sms sent search

Problem with face id on my iphone 13