Generieke formule
=SUMPRODUCT(--ISNUMBER(SEARCH(things,A1)))>0
Samenvatting
Om cellen te markeren die een van de vele tekstreeksen bevatten, kunt u een formule gebruiken die is gebaseerd op de functies ISNUMBER en ZOEKEN, samen met de functie SOMPRODUCT. In het weergegeven voorbeeld is de voorwaardelijke opmaak toegepast op B4: B11 gebaseerd op deze formule:
=SUMPRODUCT(--ISNUMBER(SEARCH(things,B4)))>0
Uitleg
Dit deel van de formule werkt van binnen naar buiten en doorzoekt elke cel in B4: B11 voor alle waarden in het benoemde bereik "dingen":
--ISNUMBER(SEARCH(things,B4)
De functie SEARCH retourneert de positie van de waarde als deze wordt gevonden, en de #VALUE-fout als deze niet wordt gevonden. Voor B4 komen de resultaten terug in een array als deze:
(8;#VALUE!;#VALUE!)
De ISNUMBER-functie verandert alle resultaten in TRUE of FALSE:
(TRUE;FALSE;FALSE)
Het dubbele negatief voor ISNUMBER dwingt TRUE / FALSE tot 1/0:
(1;0;0)
De SOMPRODUCT-functie telt vervolgens de resultaten op, die worden getest tegen nul:
=SUMPRODUCT((1;0;0))>0
Elk niet-nul resultaat betekent dat er ten minste één waarde is gevonden, dus de formule retourneert WAAR, waardoor de regel wordt geactiveerd.
Negeer lege dingen
Om lege cellen in het benoemde bereik "dingen" te negeren, kunt u een aangepaste formule als deze proberen:
=SUMPRODUCT(--ISNUMBER(SEARCH(IF(things"",things),B4)))>0
Dit werkt zolang de tekstwaarden die u aan het testen bent niet de tekenreeks "FALSE" bevatten. Als dat het geval is, kunt u de ALS-functie uitbreiden om een waarde op te nemen waarvan bekend is dat deze niet in de tekst voorkomt (bijv. "Zzzz", "####", enz.)
Hoofdlettergevoelige optie
SEARCH is niet hoofdlettergevoelig. Om ook het geval te controleren, vervangt u SEARCH door FIND als volgt:
=SUMPRODUCT(--ISNUMBER(FIND(things,A1)))>0
Voorkomen van valse overeenkomsten
Een probleem met deze benadering is dat u mogelijk valse overeenkomsten ziet die worden veroorzaakt door subtekenreeksen die in langere woorden voorkomen. Als u bijvoorbeeld "dr" probeert te matchen, kunt u ook "Andrea", "drinken", "droog", enz. Vinden, aangezien "dr" in deze woorden voorkomt. Dit gebeurt omdat SEARCH automatisch een "bevat" -overeenkomst uitvoert.
Voor een gedeeltelijke correctie kunt u spatie toevoegen rond de zoekwoorden (bijv. "Dr" of "dr") om te voorkomen dat u "dr" in een ander woord ziet. Maar dit zal mislukken als "dr" als eerste of laatste in een cel verschijnt, of naast leestekens verschijnt. Dit kan gedeeltelijk worden verholpen door ook ruimte rond de originele tekst toe te voegen. Om ruimte toe te voegen aan het begin en einde van beide tegelijk, kunt u een formule als deze proberen:
=SUMPRODUCT(--ISNUMBER(FIND(" "&things&" "," "&B4&" ")))>0
Dit lost echter geen problemen op die worden veroorzaakt door interpunctie.
Als u een completere oplossing nodig heeft, is een optie om de tekst eerst in een hulpkolom te normaliseren, waarbij u ervoor zorgt dat u ook een voorloop- en volgspatie toevoegt. Vervolgens kunt u zoeken op hele woorden omringd door spaties.
