Change the color of a cell based on another cell

small clarification: the data in R3 is larger than in P3 / Q3... for example:

R3: hello, my name is Pierre Poiré blablabla
P3: Pierre
Q3: Poiré

I want to find a way to validate if the data in P3 and/or Q3 is found within the data in R3.

Thanks!

Re,

the basic formula is to count if a name appears in the string in R3 with a simple COUNTIF
=COUNTIF(R3,P3)
if the name were at the beginning of the string one would use a wildcard *
=COUNTIF(R3,P3"*")
if the name were always at the end of the string
=COUNTIF(R3,"*"&P3)
since the name can be at the beginning, in the middle, or at the end of the string, we use two wildcards
=COUNTIF(R3,"*"&P3"*")

From there, we can play with the formula
to test if Pierre is in R3
=IF(COUNTIF(R3,"*"&P3"*"),"Pierre is in R3","Pierre is not in R3")

to test if Poiré is in R3
=IF(COUNTIF(R3,"*"&Q3"*"),"Poiré is in R3","Poiré is not in R3")

to test if either of the two is in R3
=IF(OR(COUNTIF(R3,"*"&P3"*")>=1,COUNTIF(R3,"*"&Q3"*")>=1),"Pierre or Poiré are in R3, see both","Neither of them is in R3")
--
A+
Mike-31

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