Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sammendrag
For å markere celler som inneholder en av mange tekststrenger, kan du bruke en formel basert på funksjonene ISNUMBER og SEARCH, sammen med SUMPRODUCT-funksjonen. I eksemplet som er vist, er den betingede formateringen som brukes på B4: B11 basert på denne formelen:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Forklaring
Arbeider fra innsiden og ut, denne delen av formelen søker i hver celle i B4: B11 etter alle verdier i det nevnte området "ting":
--ISNUMBER(SEARCH(things,B4)
SØK-funksjonen returnerer posisjonen til verdien hvis den ble funnet, og #VALUE-feilen hvis den ikke ble funnet. For B4 kommer resultatene tilbake i en matrise som dette:
(8;#VALUE!;#VALUE!)
ISNUMBER-funksjonen endrer alle resultatene til SANT eller FALSK:
(TRUE;FALSE;FALSE)
Det dobbelte negative foran ISNUMBER tvinger SANN / FALSK til 1/0:
(1;0;0)
SUMPRODUCT-funksjonen legger deretter opp resultatene, som testes mot null:
=SUMPRODUCT((1;0;0))>0
Ethvert resultat som ikke er null betyr at minst en verdi ble funnet, så formelen returnerer SANT, og utløser regelen.
Ignorer tomme ting
For å ignorere tomme celler i det nevnte området "ting", kan du prøve en modifisert formel som denne:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Dette fungerer så lenge tekstverdiene du tester ikke inneholder strengen "FALSE". Hvis de gjør det, kan du utvide IF-funksjonen til å inkludere en verdi hvis falsk kjent for ikke å forekomme i teksten (dvs. "zzzz", "####", etc.)
Saksfølsomt alternativ
SØK er ikke mellom store og små bokstaver. For å sjekke saken også, erstatt SØK med FINN slik:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Forebygge falske kamper
Et problem med denne tilnærmingen er at du kan se falske treff forårsaket av understreng som vises i lengre ord. Hvis du for eksempel prøver å matche "dr", kan du også finne "Andrea", "drikke", "tørr" osv. Siden "dr" vises inne i disse ordene. Dette skjer fordi SEARCH automatisk utfører en "inneholder" -kamp.
For en delvis løsning kan du legge til mellomrom rundt søkeordene (dvs. "dr" eller "dr") for å unngå å fange "dr" i et annet ord. Men dette vil mislykkes hvis "dr" vises først eller sist i en celle, eller vises ved siden av tegnsetting. Dette kan løses delvis ved å legge til plass også rundt originalteksten. For å legge til plass til starten og slutten av begge samtidig, kan du prøve en formel som denne:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Dette løser imidlertid ikke problemer forårsaket av tegnsetting.
Hvis du trenger en mer komplett løsning, er ett alternativ å normalisere teksten først i en hjelpekolonne, og passe på å også legge til et ledende og etterfølgende rom. Deretter kan du søke etter hele ord omgitt av mellomrom.
