Fórmula genérica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Resumo
Para destacar células que contenham uma das muitas cadeias de texto, você pode usar uma fórmula baseada nas funções ISNUMBER e SEARCH, junto com a função SUMPRODUCT. No exemplo mostrado, a formatação condicional aplicada a B4: B11 é baseada nesta fórmula:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Explicação
Trabalhando de dentro para fora, esta parte da fórmula pesquisa cada célula em B4: B11 para todos os valores no intervalo nomeado "coisas":
--ISNUMBER(SEARCH(things,B4)
A função SEARCH retorna a posição do valor se encontrado, eeo erro #VALUE se não encontrado. Para B4, os resultados voltam em uma matriz como esta:
(8;#VALUE!;#VALUE!)
A função ISNUMBER altera todos os resultados para TRUE ou FALSE:
(TRUE;FALSE;FALSE)
O duplo negativo na frente de ISNUMBER força TRUE / FALSE a 1/0:
(1;0;0)
A função SUMPRODUCT soma os resultados, que são testados em relação a zero:
=SUMPRODUCT((1;0;0))>0
Qualquer resultado diferente de zero significa que pelo menos um valor foi encontrado, portanto, a fórmula retorna TRUE, disparando a regra.
Ignore coisas vazias
Para ignorar células vazias no intervalo nomeado "coisas", você pode tentar uma fórmula modificada como esta:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Isso funciona desde que os valores de texto que você está testando não contenham a string "FALSE". Em caso afirmativo, você pode estender a função IF para incluir um valor caso se saiba que falso não ocorre no texto (ou seja, "zzzz", "####", etc.)
Opção que diferencia maiúsculas de minúsculas
SEARCH não faz distinção entre maiúsculas e minúsculas. Para verificar o caso também, substitua SEARCH por FIND assim:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Prevenindo falsas correspondências
Um problema com essa abordagem é que você pode ver correspondências falsas causadas por substrings que aparecem dentro de palavras mais longas. Por exemplo, se tentar corresponder "dr", também poderá encontrar "Andrea", "drink", "dry", etc., uma vez que "dr" aparece dentro destas palavras. Isso acontece porque SEARCH executa automaticamente uma correspondência "contém".
Para uma correção parcial, você pode adicionar um espaço ao redor das palavras de pesquisa (por exemplo, "dr" ou "dr") para evitar capturar "dr" em outra palavra. Mas isso falhará se "dr" aparecer primeiro ou por último em uma célula, ou ao lado da pontuação. Isso pode ser parcialmente resolvido adicionando espaço também ao redor do texto original. Para adicionar espaço no início e no final de ambos ao mesmo tempo, você pode tentar uma fórmula como esta:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
No entanto, isso não corrigirá problemas causados por pontuação.
Se você precisar de uma solução mais completa, uma opção é normalizar o texto primeiro em uma coluna auxiliar, tomando cuidado para adicionar um espaço à esquerda e à direita. Então você pode pesquisar por palavras inteiras cercadas por espaços.
