Kim hat eine Datenspalte, die einen Standortcode enthält. Dieser Code besteht aus einem einzelnen Buchstaben, gefolgt von zwei Ziffern, z. B. A03 oder B12.

Kim möchte die Spalte bedingt formatieren, damit alles, was in die Spalte eingegeben wird und dieses Muster nicht verwendet, auf irgendeine Weise hervorgehoben wird.

Es gibt viele Möglichkeiten, wie dieses Problem angegangen werden kann. Jeder Ansatz hängt von der Entwicklung einer Formel ab, die innerhalb einer bedingten Formatierungsregel verwendet werden kann, um entweder True oder False zurückzugeben und das bedingte Format auszulösen. (Dieser Tipp befasst sich nicht mit dem Erstellen einer bedingten Formatierungsregel, sondern konzentriert sich auf die verschiedenen Formeln, die in der Regel verwendet werden können. Wie Sie bedingte Formatierungsregeln erstellen, wird in anderen ExcelTips behandelt.)

Welche Formel auch immer zusammengestellt wird, muss drei Dinge testen:

  • Die Zeichenfolge enthält genau drei Zeichen.

  • Das erste Zeichen ist ein Buchstabe.

  • Das zweite und dritte Zeichen sind Ziffern.

Es ist ziemlich einfach herauszufinden, ob der Text in einer Zelle nur drei Zeichen enthält. Sie können dazu die LEN-Funktion verwenden:

=LEN(A1)=3

Es ist auch ziemlich einfach herauszufinden, ob das erste Zeichen ein Buchstabe ist. Tatsächlich gibt es verschiedene Möglichkeiten. Jedes der folgenden Zeichen gibt True zurück, wenn das erste Zeichen ein Buchstabe ist:

=AND(CODE(LEFT(A1,1))>64,CODE(LEFT(A1,1))<91)

=AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z")

Diese prüfen, ob sich nur ein Großbuchstabe an der ersten Stelle befindet. Wenn Sie auch Kleinbuchstaben akzeptieren möchten, können Sie eine Variation des zweiten Tests verwenden:

=AND(UPPER(LEFT(A1,1))>="A",UPPER(LEFT(A1,1))<="Z")

Wenn sowohl Groß- als auch Kleinbuchstaben zulässig sind (zusammen mit praktisch jedem anderen Symbol), können Sie den folgenden Test verwenden:

=NOT(ISNUMBER(LEFT(A1,1)+0))

Hier sind einige Möglichkeiten, wie Sie den dritten Test anwenden können – ob das zweite und dritte Zeichen Ziffern sind:

=ISNUMBER(VALUE(RIGHT(A1,2)))

=ISNUMBER(--RIGHT(A1,2))

Beachten Sie, dass diese Ansätze die letzten beiden Zeichen zusammen behandeln. Dies bedeutet, dass „1“, „11“ und „111“ den Test bestehen würden – sie werden erfolgreich als Zahlen ausgecheckt. Wenn Ihre Formel nur die letzten beiden Ziffern überprüft, könnte dies ein Problem sein, aber die Tatsache, dass Sie auch die erste Überprüfung einschließen (für die Gesamtlänge der Zeichenfolge in der Zelle und dass sie 3 sein muss), ist dies nicht der Fall Ich stelle überhaupt kein Problem dar.

Der Trick besteht nun darin, den Ansatz Ihrer Wahl für jeden der drei Tests in einer einzigen Formel zu kombinieren. Dies kann mit der UND-Funktion erfolgen. Ich werde einfach den kürzesten aus jedem der Tests auswählen und sie auf folgende Weise kombinieren:

=AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2)))

Wie geschrieben, gibt diese Formel True zurück, wenn alle Tests bestanden wurden. Dies bedeutet, dass die Zelle einen Standortcode mit einem gültigen Muster enthält. Dies würde als bedingtes Format hervorragend funktionieren, wenn Kim die Spalte als Farbe (z. B. Grün) formatieren und dann das bedingte Format verwenden würde, um die grüne Farbe zu entfernen. Dies scheint rückwärts zu sein, und Sie möchten möglicherweise nur dann ein Format anwenden, wenn das Muster nicht erfüllt ist. Wenn dies der Fall ist, schließen Sie die Formel einfach in eine NOT-Funktion ein, um das zurückgegebene True / False umzukehren:

=NOT(AND(LEN(A1)=3, AND(LEFT(A1,1)>="A",LEFT(A1,1)<="Z"), ISNUMBER(--RIGHT(A1,2))))

Wie Sie sehen, kann die Verwendung einer solchen Formel etwas schwierig sein. Wenn Sie möchten, können Sie eine UDF (benutzerdefinierte Funktion) erstellen, die die bedingte Formatierungsregel etwas kürzer macht. Das folgende Makro ist ein guter Weg:

Function IsBadPattern(sCell As String) As Boolean     IsBadPattern = Not(sCell Like "[A-Z][0-9][0-9]")

End Function

Um die UDF in Ihrer bedingten Formatierungsregel zu verwenden, müssen Sie lediglich die folgende Formel verwenden:

=IsBadPattern(A1)

Das Ergebnis der UDF ist TRUE, wenn die Zeichenfolge in der referenzierten Zelle nicht mit dem gewünschten Muster übereinstimmt. Wie geschrieben, ist die Verwendung von Kleinbuchstaben an der ersten Zeichenposition nicht zulässig. Wenn Sie Kleinbuchstaben zulassen müssen, müssen Sie die UDF nicht ändern. Ändern Sie stattdessen die Formel wie folgt:

=IsBadPattern(UPPER(A1))

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (9976) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365.