Conditional formatting rule for A1:B3:

=ISERROR(SUMPRODUCT(FIND(" "&FILTERXML("<t><s>"&SUBSTITUTE($B1," ","</s><s>")&"</s></t>","//s")&" "," "&$A1&" ")))

But since conditional formatting is volatile I don't know if it's wiser to use conditional formatting or just apply the formula to a 3rd column if you are using it on a large dataset. Up to you.

CodePudding user response：

As you might need to run through all entries within one cell, I'm afraid that you might need VBA for solving this one. It would mean that you write an UDF (User-Defined Function) for doing the checking and use that as a basis for a formula for your conditional formatting.

Your function should be something like (not tested):

Function chars_inside_string(characters, inside_string) As Boolean
Dim present As Boolean
present = False
test = Split(characters, "-")
For i = 1 To UBound(test)
  present = present Or (InStr(test(i), inside_string) > 0)
Next i
chars_inside_string = present
End Function