Generisk formel
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Sammendrag
For å teste en celle for å se om den inneholder en av mange strenger, kan du bruke en formel basert på funksjonene SØK, ISNUMBER og SUMPRODUKT. Formelen i C5, kopiert ned, er:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
der ting er det navngitte området E5: E9.
Forklaring
Vi ønsker å teste hver celle i B5: B11 for å se om det inneholder noen av strengene i de navngitte range ting (E5: E9). Formelen vi bruker i C5, kopiert ned, er:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B5)))>0
Denne formelen er basert på en formel (forklart her) som sjekker en celle for en enkelt understreng. Hvis cellen inneholder undergrunnen, returnerer formelen SANT. Hvis ikke, returnerer formelen FALSE:
ISNUMBER(SEARCH(things,B5))
I dette tilfellet gir vi imidlertid SØK en liste over strenger. Siden det er 5 strenger i ting , returnerer SEARCH 5 resultater i en matrise som dette:
(1;#VALUE!;#VALUE!;#VALUE!;#VALUE!)
Når SEARCH finner en streng, returnerer den posisjonen til den strengen. Hvis SEARCH ikke finner en streng, returnerer den en #VALUE! feil. Fordi "gul" vises som det første ordet i B5, ser vi en 1. Fordi de andre strengene ikke blir funnet, er de andre 4 elementene feil.
Denne matrisen returneres direkte til ISNUMBER-funksjonen. ISNUMBER returnerer deretter en matrise med SANNE / FALSE verdier:
(TRUE;FALSE;FALSE;FALSE;FALSE)
Hvis vi har en sann i matrisen, vet vi at en celle inneholder minst en av strengene vi leter etter. Den enkleste måten å sjekke for SANT er å legge alle verdiene sammen. Vi kan gjøre det med SUMPRODUCT, men først må vi tvinge SANNE / FALSE verdiene til 1s og 0s med en dobbel negativ (-) slik:
--ISNUMBER(SEARCH(things,B5))
Dette gir en ny matrise som bare inneholder 1 og 0:
(1;0;0;0;0)
levert direkte til SUMPRODUCT:
=SUMPRODUCT((1;0;0;0;0))
Med bare en matrise å behandle, legger SUMPRODUCT til elementene i matrisen og returnerer et resultat. Ethvert resultat som ikke er null betyr at vi har et "hit", så vi legger til> 0 for å tvinge et endelig resultat av SANT eller FALSK:
=SUMPRODUCT((1;0;0;0;0))>0 // returns TRUE
Med en hardkodet liste
Det er ikke nødvendig å bruke et område for listen over strenger å se etter. Du kan også bruke en matrisekonstant. For eksempel, for å se etter "rød", "blå" og "grønn", kan du bruke en formel som denne:
=SUMPRODUCT(--ISNUMBER(SEARCH(("red","blue","green"),B5)))>0
Forebygge falske kamper
Et problem med denne tilnærmingen er at du kan få falske treff fra 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 samsvarer med "inneholder".
For en rask hacking 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 med tegnsetting.
Hvis du trenger en mer nøyaktig løsning, er et alternativ å normalisere teksten først i en hjelpekolonne, og passe på å også legge til et ledende og etterfølgende rom. Deretter bruker du formelen på denne siden på den resulterende teksten.
