Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sammanfattning
För att testa en cell för att se om den innehåller en av många strängar kan du använda en formel baserad på funktionerna SÖK, ISNUMBER och SUMPRODUCT. Formeln i C5, kopierad ner, är:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
där saker är namngivna intervall E5: E9.
Förklaring
Vi vill testa varje cell i B5: B11 för att se om den innehåller någon av strängarna i namngivna saker (E5: E9). Formeln vi använder i C5, kopierad ner, är:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Denna formel är baserad på en formel (förklaras här) som kontrollerar en cell för en enda substring. Om cellen innehåller understrängen returnerar formeln SANT. Om inte, returnerar formeln FALSE:
ISNUMBER(SEARCH(things,B5))
Men i det här fallet ger vi SÖK en lista med strängar. Eftersom det finns 5 strängar i saker returnerar SEARCH 5 resultat i en array som denna:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
När SEARCH hittar en sträng returnerar den strängens position. Om SEARCH inte hittar en sträng returnerar den #VALUE! fel. Eftersom "gul" visas som det första ordet i B5, ser vi en 1. Eftersom de andra strängarna inte hittas är de andra fyra objekten fel.
Denna matris returneras direkt till ISNUMBER-funktionen. ISNUMBER returnerar sedan en matris med SANT / FALSKA värden:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Om vi till och med har en SANT i matrisen vet vi att en cell innehåller minst en av strängarna vi letar efter. Det enklaste sättet att kontrollera om SANT är att lägga till alla värden tillsammans. Vi kan göra det med SUMPRODUCT, men först måste vi tvinga SANT / FALSKA värden till 1s och 0s med en dubbel negativ (-) så här:
--ISNUMBER(SEARCH(things,B5))
Detta ger en ny array som bara innehåller 1s och 0s:
(1;0;0;0;0)
levereras direkt till SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Med bara en matris att bearbeta lägger SUMPRODUCT till objekten i matrisen och returnerar ett resultat. Alla resultat som inte är noll betyder att vi har en "hit", så vi lägger till> 0 för att tvinga ett slutresultat av SANT eller FALSK:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Med en hårdkodad lista
Det är inte nödvändigt att använda ett intervall för listan över strängar att leta efter. Du kan också använda en arraykonstant. Om du till exempel vill söka efter "rött", "blått" och "grönt" kan du använda en sådan formel:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Förhindra falska matchningar
Ett problem med detta tillvägagångssätt är att du kan få falska matchningar från understrängar 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 gör en "innehåller" matchning.
För en snabb hackning 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 med skiljetecken.
Om du behöver en mer exakt lösning är ett alternativ att normalisera texten först i en hjälpkolumn, var noga med att lägga till ett ledande och efterföljande utrymme. Sedan använder du formeln på den här sidan på den resulterande texten.
