Formula generica
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sommario
Per evidenziare le celle che contengono una delle tante stringhe di testo, puoi utilizzare una formula basata sulle funzioni ISNUMBER e SEARCH, insieme alla funzione SUMPRODUCT. Nell'esempio mostrato, la formattazione condizionale applicata a B4: B11 si basa su questa formula:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Spiegazione
Lavorando dall'interno verso l'esterno, questa parte della formula ricerca ogni cella in B4: B11 per tutti i valori nell'intervallo denominato "cose":
--ISNUMBER(SEARCH(things,B4)
La funzione RICERCA restituisce la posizione del valore se trovato e l'errore #VALORE se non trovato. Per B4, i risultati tornano in una matrice come questa:
(8;#VALUE!;#VALUE!)
La funzione ISNUMBER modifica tutti i risultati in TRUE o FALSE:
(TRUE;FALSE;FALSE)
Il doppio negativo davanti a ISNUMBER forza TRUE / FALSE a 1/0:
(1;0;0)
La funzione SUMPRODUCT quindi somma i risultati, che vengono verificati rispetto a zero:
=SUMPRODUCT((1;0;0))>0
Qualsiasi risultato diverso da zero significa che è stato trovato almeno un valore, quindi la formula restituisce VERO, attivando la regola.
Ignora le cose vuote
Per ignorare le celle vuote nell'intervallo denominato "cose", puoi provare una formula modificata come questa:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Funziona a condizione che i valori di testo che stai testando non contengano la stringa "FALSE". In tal caso, puoi estendere la funzione IF per includere un valore se è noto che falso non si trova nel testo (ad esempio "zzzz", "####", ecc.)
Opzione case-sensitive
SEARCH non distingue tra maiuscole e minuscole. Per controllare anche il caso, sostituire CERCA con TROVA in questo modo:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Prevenire le false corrispondenze
Un problema con questo approccio è che potresti vedere false corrispondenze causate da sottostringhe che appaiono all'interno di parole più lunghe. Ad esempio, se provi ad abbinare "dr" potresti trovare anche "Andrea", "drink", "dry", ecc. Poiché "dr" appare all'interno di queste parole. Ciò accade perché la RICERCA esegue automaticamente una corrispondenza "contiene".
Per una correzione parziale, è possibile aggiungere uno spazio intorno alle parole di ricerca (ad esempio "dr" o "dr") per evitare di catturare "dr" in un'altra parola. Ma questo fallirà se "dr" appare per primo o per ultimo in una cella, o appare accanto alla punteggiatura. Questo può essere parzialmente risolto aggiungendo spazio anche intorno al testo originale. Per aggiungere spazio all'inizio e alla fine di entrambi allo stesso tempo, puoi provare una formula come questa:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Tuttavia, questo non risolverà i problemi causati dalla punteggiatura.
Se hai bisogno di una soluzione più completa, un'opzione è normalizzare il testo prima in una colonna helper, avendo cura di aggiungere anche uno spazio iniziale e uno finale. Quindi puoi cercare parole intere circondate da spazi.
