Excel-Formel: Prüfen, ob eine Zelle bestimmte Texte enthält, aber andere nicht

Um zu prüfen, ob eine Zelle Texte aus dem Bereich A enthält, aber keine Texte aus dem Bereich B, verwenden Sie eine Arrayformel, die in Excel die Funktionen COUNT, SEARCH und AND kombiniert. Dieses Tutorial zeigt Ihnen Schritt für Schritt, wie Sie diese leistungsstarke Formel effektiv einsetzen.

Allgemeine Formel:

Argumente

Rückgabewert:

Diese Formel gibt einen logischen Wert zurück: TRUE, wenn die Zelle mindestens eines der „Include“-Argumente enthält, aber keines der „Exclude“-Argumente; andernfalls FALSE.

Funktionsweise dieser Formel

Angenommen, Sie möchten prüfen, ob die Zelle B3 einen der Werte aus dem Bereich E3:E5 enthält, aber gleichzeitig keinen der Werte aus dem Bereich F3:F4 – verwenden Sie dazu die folgende Formel:

Drücken Sie Umschalt + Strg + Eingabetaste, um die Zelle zu prüfen.

Erläuterung

Teil 1: ( COUNT(SEARCH($E$3:$E$5,B3))>0) prüft, ob Zelle B3 einen der Werte im Bereich E3:E5 enthält

SEARCH-Funktion: Die SEARCH-Funktion gibt die Position des ersten Zeichens einer Textzeichenfolge innerhalb einer anderen zurück. Wird der gesuchte Text gefunden, liefert sie dessen relative Position; andernfalls den Fehlerwert #WERT!. In diesem Beispiel sucht die Formel SEARCH($E$3:$E$5,B3) jeden Wert aus dem Bereich E3:E5 in Zelle B3 und gibt die jeweilige Position der gefundenen Textzeichenfolgen in B3 zurück. Das Ergebnis ist ein Array wie folgt: {1;7;#VALUE!}.

COUNT-Funktion: Die COUNT-Funktion zählt die Anzahl der numerischen Werte in einem Bereich oder Array. COUNT(SEARCH($E$3:$E$5,B3)) gibt 2 zurück, da das Array {1;7;#VALUE!} zwei gültige numerische Elemente enthält.

COUNT(SEARCH($E$3:$E$5,B3))>0Vergleicht das Ergebnis der COUNT-Funktion mit null: Solange das Ergebnis größer als 0 ist, wird TRUE zurückgegeben; andernfalls FALSE. In diesem Fall wird TRUE zurückgegeben.

Teil 2: COUNT(SEARCH($F$3:$F$4,B3))=0 prüft, ob Zelle B3 keinen der Werte im Bereich F3:F4 enthält

SEARCH-Funktion: Die SEARCH-Funktion gibt die Position des ersten Zeichens einer Textzeichenfolge innerhalb einer anderen zurück. Wird der gesuchte Text gefunden, liefert sie dessen relative Position; andernfalls den Fehlerwert #WERT!. In diesem Fall sucht die Formel SEARCH($F$3:$F$4,B3) jeden Wert aus dem Bereich F3:F4 in Zelle B3 und gibt die jeweilige Position der Textzeichenfolgen in B3 zurück. Das Ergebnis ist ein Array wie folgt:{#VALUE!; #VALUE!;#VALUE!}.

COUNT-Funktion: Die COUNT-Funktion zählt die Anzahl der numerischen Werte in einem Bereich oder Array. COUNT(SEARCH($F$3:$F$4,B3)) gibt 0 zurück, da sich keine gültigen Werte im Array {#VALUE!; #VALUE!; #VALUE!} befinden.

COUNT(SEARCH($F$3:$F$4,B3))>0Vergleicht das Ergebnis der COUNT-Funktion mit null: Solange das Ergebnis der COUNT-Funktion gleich 0 ist, wird TRUE zurückgegeben; andernfalls FALSE. In diesem Fall wird TRUE zurückgegeben.

Teil 3:AND-Funktion

=AND(COUNT(SEARCH($E$3:$E$5,B3))>0,COUNT(SEARCH($F$3:$F$4,B3))=0)

=AND(TRUE,TRUE)

=TRUE

Beispieldatei

Klicken Sie, um die Beispieldatei herunterzuladen

Verwandte Formeln

• Prüfen, ob eine Zelle einen bestimmten Text enthält
  Um zu prüfen, ob eine Zelle Texte aus dem Bereich A enthält, aber keine Texte aus dem Bereich B, verwenden Sie eine Arrayformel, die die Funktionen COUNT, SEARCH und AND in Excel kombiniert.
• Prüfen, ob eine Zelle einen von mehreren Werten enthält, aber andere Werte ausschließt
  In diesem Tutorial erhalten Sie eine Formel, mit der Sie diese Aufgabe im Handumdrehen erledigen können: Prüfen Sie, ob eine Zelle einen der angegebenen Werte enthält – und gleichzeitig alle anderen Werte ausschließt. Außerdem erklären wir Ihnen Schritt für Schritt die einzelnen Formelargumente.
• Prüfen, ob eine Zelle einen von mehreren Werten enthält
  Angenommen, in Excel befindet sich eine Liste von Werten in Spalte E. Sie möchten überprüfen, ob die Zellen in Spalte B mindestens einen der Werte aus Spalte E enthalten, und entsprechend TRUE oder FALSE zurückgeben.
• Prüfen, ob eine Zelle eine Zahl enthält
  Manchmal möchten Sie wissen, ob eine Zelle eine Zahl enthält. In diesem Tutorial stellen wir Ihnen eine Formel vor, die TRUE zurückgibt, wenn dies der Fall ist – andernfalls FALSE.