Vishwajeet hat eine Reihe von Teilenummern in einer Spalte. Diese Teilenummern müssen einem bestimmten Muster folgen (2 Zahlen, 5 Buchstaben, 4 Zahlen, 1 Buchstabe, 1 Zahl, 1 Buchstabe und 1 Zahl). Er fragt sich, ob es eine Möglichkeit gibt, leicht zu identifizieren, welche der Zellen in der Spalte von diesem Muster abweichen.

Abhängig von der tatsächlichen Natur Ihrer Daten gibt es eine Reihe von Möglichkeiten, wie Sie sich dieser Aufgabe nähern können. Sie können beispielsweise eine Formel wie diese in einer Hilfsspalte verwenden:

=AND(LEN(A1)=15,ISNUMBER(--LEFT(A1,2)),ISTEXT(MID(A1,3,5)), ISNUMBER(--MID(A1,8,4)),ISTEXT(MID(A1,12,1)),ISNUMBER(-- MID(A1,13,1)),ISTEXT(MID(A1,14,1)),ISNUMBER(--RIGHT(A1,1)))

Die Formel (die ziemlich lang ist) gibt True oder False zurück, je nachdem, ob das Muster korrekt ist oder nicht. Es gibt jedoch ein Problem mit der Formel. Es werden keine Symbole abgefangen, die anstelle von Buchstaben verwendet werden (z. B. ein Dollarzeichen oder ein Sternchen), und es werden keine Symbole abgefangen, die anstelle von Zahlen verwendet werden (z. B. ein Punkt oder ein Prozentzeichen). Der Grund dafür ist, dass die ISTEXT-Funktion Symbole als Text betrachtet und die ISNUMBER-Funktion so etwas wie „1.23“ als Zahl analysiert.

Wenn Sie diese missbräuchliche Verwendung von Symbolen abfangen möchten, kann die folgende Formel verwendet werden:

=AND(LEN(A1)=15,ISNUMBER(SUM(SEARCH(MID(A1,{1,2,8,9,10,11, 13,15},1),"0123456789"),SEARCH(MID(A1,{3,4,5,6,7,12,14},1), "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))))

Dieser funktioniert, weil er die SEARCH-Funktion verwendet, um jedes Zeichen in der Teilenummer effektiv zu überprüfen. Man könnte meinen, Sie müssten die Formel möglicherweise als Array-Formel eingeben (mit Strg + Umschalt + Eingabetaste beenden), aber interessanterweise bekomme ich keinen Unterschied in den Ergebnissen, wenn ich sie als reguläre Formel im Vergleich zu einer Array-Formel verwende.

Wenn Sie die Teilenummernmuster häufig überprüfen müssen, sollten Sie ein Makro verwenden, um die Überprüfung durchzuführen. Das Folgende ist eine kurze benutzerdefinierte Funktion, die den Like-Operator verwendet, um zu sehen, ob das Muster befolgt wird.

Function CheckPattern(rCell As Range) As Boolean     Dim sPattern As String

sPattern = "##[A-Z][A-Z][A-Z][A-Z][A-Z]####[A-Z]#[A-Z]#"



CheckPattern = rCell.Value Like sPattern End Function

Beachten Sie die Verwendung der Variablen sPattern. Dies ist das Muster, dem zu folgen ist, wenn der Like-Operator seinen Vergleich durchführt. Jedes Auftreten des # -Symbols bedeutet, dass sich jede Ziffer an dieser Position befinden kann. Jedes Auftreten von [A-Z] bedeutet, dass die Position ein Buchstabe im Bereich von A bis Z sein kann.

Weitere Informationen zur Bedeutung der Zeichen, die Sie in das Muster aufnehmen können, finden Sie auf dieser Seite auf einer der Microsoft-Websites:

https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/operators/like-operator

Diese Site ist eigentlich für Visual Basic gedacht, nicht für VBA, aber diese spezielle Informationsseite funktioniert in VBA einwandfrei.

Um die benutzerdefinierte Funktion CheckPattern verwenden zu können, können Sie Folgendes in eine beliebige Zelle Ihres Arbeitsblatts einfügen:

=CheckPattern(A1)

Dies setzt voraus, dass sich die Teilenummer in Zelle A1 befindet, ebenso wie alle anderen in diesem Tipp aufgeführten Formeln.

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

Dieser Tipp (3391) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.