Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sammanfattning
För att markera celler som innehåller en av många textsträngar kan du använda en formel baserad på funktionerna ISNUMBER och SÖK, tillsammans med SUMPRODUCT-funktionen. I exemplet som visas är den villkorliga formateringen som tillämpas på B4: B11 baserad på denna formel:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Förklaring
Arbetar inifrån och ut, den här delen av formeln söker efter varje cell i B4: B11 för alla värden i det angivna intervallet "saker":
--ISNUMBER(SEARCH(things,B4)
SÖK-funktionen returnerar positionen för värdet om det hittas, och #VALUE-felet om det inte hittas. För B4 kommer resultaten tillbaka i en array som denna:
(8;#VALUE!;#VALUE!)
ISNUMBER-funktionen ändrar alla resultat till SANT eller FALSKT:
(TRUE;FALSE;FALSE)
Det dubbla negativet framför ISNUMBER tvingar SANT / FALSKT till 1/0:
(1;0;0)
SUMPRODUCT-funktionen adderar sedan resultaten, som testas mot noll:
=SUMPRODUCT((1;0;0))>0
Alla resultat som inte är noll betyder att minst ett värde hittades, så formeln returnerar SANT och utlöser regeln.
Ignorera tomma saker
För att ignorera tomma celler i det namngivna intervallet "saker" kan du prova en modifierad formel så här:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Detta fungerar så länge de textvärden du testar inte innehåller strängen "FALSE". Om de gör det kan du utöka IF-funktionen till att inkludera ett värde om falskt är känt för att inte förekomma i texten (t.ex. "zzzz", "####", etc.)
Skiftlägeskänsligt alternativ
SÖK är inte skiftlägeskänslig. För att även kontrollera fallet, ersätt SÖK med HITTA så:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Förhindra falska matchningar
Ett problem med detta tillvägagångssätt är att du kan se falska matchningar orsakade av underlag som visas i längre ord. Om du till exempel försöker matcha "dr" kan du också hitta "Andrea", "drink", "dry", etc. eftersom "dr" visas i dessa ord. Detta händer eftersom SEARCH automatiskt utför en "innehåller" -matchning.
För en delvis korrigering kan du lägga till utrymme runt sökorden (dvs. "dr" eller "dr") för att undvika att fånga "dr" i ett annat ord. Men detta misslyckas om "dr" visas först eller sist i en cell, eller visas bredvid skiljetecken. Detta kan delvis åtgärdas genom att lägga till utrymme också runt originaltexten. För att lägga till utrymme i början och slutet av båda samtidigt kan du prova en sådan formel:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Detta kommer dock inte att lösa problem som orsakas av skiljetecken.
Om du behöver en mer komplett lösning är ett alternativ att normalisera texten först i en hjälpkolumn, var noga med att också lägga till ett ledande och efterföljande utrymme. Sedan kan du söka efter hela ord omgiven av mellanslag.
