Conditional formatting with an IF function

[Indaxt]

Good evening everyone, I have been trying for a while to create something and I just can't make it work! I have tried as a table and not as a table. I have tried with conditional formating with the IF function but no luck either.

In the example below we have several text values in column B with different brands followed by numbers. I would like column C to show a text based on column B content.
Example: If B1 and B4 contain "Leroy..." I want column C to give me for both C1 and C4 "LEROY".

I honestly don't know how to get around this problem. I hope it's something really simple and I'm just being thick!

Thank you for your time.

A	B	C
1	Leroy Merlin 3123124
2	Primark 13324
3	Primark 7723
4	Leroy Merlin 22220
5	Mango 338972

 

[awoohaw]

try this:

Mr Excel Questions 73.xlsm
	A	B	C
1	A	B	C
2	1	Leroy Merlin 3123124	Leroy
3	2	Primark 13324
4	3	Primark 7723
5	4	Leroy Merlin 22220	Leroy
6	5	Mango 338972
Indaxt

Cell Formulas
Range	Formula
C2:C6	C2	=IF(ISNUMBER(FIND("LEROY",UPPER(B2))),"Leroy","")

If you're trying to conditional format column C then conditional format formula for cell C2 would be:

=ISNUMBER(FIND("LEROY",UPPER(B2)))

 

[Indaxt]

Good evening @awoohaw and thank you very much for your quick response!

It's working perfectly in the cells and the conditional format is working if I try to apply colors or borders but, can I conditional format that column to give me "Leroy" or "Primark" in those cells or can I only format it for colors/borders/...? Is it possible?

Sorry, I feel like this is such a newbie question.

I appreciate your time man. Thanks again!

 

[awoohaw]

You're welcome. You will need to do individual rules for each condition.
I only did colors, you update the CF as you see fit.

Mr Excel Questions 73.xlsm
	A	B	C
1	A	B	C
2	1	Leroy Merlin 3123124	Leroy
3	2	Primark 13324	Primark
4	3	Primark 7723	Primark
5	4	Leroy Merlin 22220	Leroy
6	5	Mango 338972
Indaxt

Cell Formulas
Range	Formula
C2:C6	C2	=IF(ISNUMBER(FIND("LEROY",UPPER(B2))),"Leroy",IF(ISNUMBER(FIND("PRIMARK",UPPER(B2))),"Primark",""))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C2:C6	Expression	=ISNUMBER(FIND("PRIMARK",UPPER(B2)))	text	NO
C2:C6	Expression	=ISNUMBER(FIND("LEROY",UPPER(B2)))	text	NO