Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Resumé
For at fremhæve celler, der indeholder en af mange tekststrenge, kan du bruge en formel baseret på funktionerne ISNUMBER og SEARCH sammen med SUMPRODUCT-funktionen. I det viste eksempel er den betingede formatering anvendt på B4: B11 baseret på denne formel:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Forklaring
Arbejder indefra og ud, denne del af formlen søger i hver celle i B4: B11 efter alle værdier i det navngivne område "ting":
--ISNUMBER(SEARCH(things,B4)
Funktionen SØG returnerer positionen for værdien, hvis den findes, og fejlen #VALUE, hvis den ikke findes. For B4 kommer resultaterne tilbage i en matrix som denne:
(8;#VALUE!;#VALUE!)
ISNUMBER-funktionen ændrer alle resultater til SAND eller FALSK:
(TRUE;FALSE;FALSE)
Det dobbelte negative foran ISNUMBER tvinger SAND / FALSK til 1/0:
(1;0;0)
SUMPRODUCT-funktionen tilføjer derefter resultaterne, som testes mod nul:
=SUMPRODUCT((1;0;0))>0
Ethvert resultat, der ikke er nul, betyder, at der blev fundet mindst en værdi, så formlen returnerer SAND og udløser reglen.
Ignorer tomme ting
For at ignorere tomme celler i det navngivne interval "ting" kan du prøve en ændret formel som denne:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Dette fungerer, så længe de tekstværdier, du tester, ikke indeholder strengen "FALSE". Hvis de gør det, kan du udvide IF-funktionen til at omfatte en værdi, hvis falsk vides ikke at forekomme i teksten (dvs. "zzzz", "####" osv.)
Skift mellem store og små bogstaver
SEARCH er ikke store og små bogstaver. For også at kontrollere sagen skal du udskifte SEARCH med FIND sådan:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Forebyggelse af falske kampe
Et problem med denne fremgangsmåde er, at du muligvis kan se falske matches forårsaget af understrenge, der vises i længere ord. For eksempel, hvis du prøver at matche "dr", kan du også finde "Andrea", "drikke", "tør" osv., Da "dr" vises inde i disse ord. Dette sker, fordi SEARCH automatisk udfører en "indeholder" match.
For en delvis rettelse kan du tilføje mellemrum omkring søgeordene (dvs. "dr" eller "dr") for at undgå at fange "dr" i et andet ord. Men dette mislykkes, hvis "dr" vises først eller sidst i en celle eller vises ved siden af tegnsætning. Dette kan delvist løses ved at tilføje plads også omkring den originale tekst. For at tilføje plads til starten og slutningen af begge på samme tid kan du prøve en formel som denne:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Dette løser dog ikke problemer forårsaget af tegnsætning.
Hvis du har brug for en mere komplet løsning, er en mulighed at normalisere teksten først i en hjælpekolonne, idet du også sørger for at tilføje et ledende og efterfølgende rum. Derefter kan du søge efter hele ord omgivet af mellemrum.
