Steve hat ein Arbeitsblatt, das über zehntausend Zeilen enthält, wobei jede Zelle in Spalte A einen Dateinamen enthält. Diese Namen müssen zwei Regeln folgen, und Steve muss herausfinden, welche Namen gegen eine der Regeln verstoßen. Wenn ein Dateiname einen Bindestrich enthält, muss vor und nach dem Bindestrich ein Leerzeichen stehen. Die zweite Regel lautet: Wenn der Name ein Komma enthält, darf kein Leerzeichen davor stehen, sondern nur ein Leerzeichen danach.

Steve fragt sich, wie er Zellen hervorheben kann, die gegen eine (oder beide)

verstoßen dieser Regeln.

Immer wenn jemand erwähnt, dass er etwas in einem Arbeitsblatt „hervorheben“ möchte, denken die meisten Menschen daran, eine bedingte Formatierung zu verwenden. Diese Instanz ist keine Ausnahme. Sie können problemlos die bedingte Formatierung verwenden, um die Musterverletzungen hervorzuheben. Der Schlüssel zum Entwickeln der bedingten Formatierungsregel besteht darin, eine Formel zu erstellen, die True zurückgibt, wenn das Muster verletzt wird. Diese Formel prüft auf beide Verstöße:

=OR(ISNUMBER(FIND("-",SUBSTITUTE(A1," - ",""))), ISNUMBER(FIND(",",SUBSTITUTE(A1,", ",""))), ISNUMBER(FIND(" ,",A1)))

Ich habe die Formel hier in drei Zeilen aufgeteilt, aber es sollte als eine vollständige Formel betrachtet werden. Die Formel entfernt die richtigen Muster (Leerzeichen, Bindestrich, Leerzeichen und Komma, Leerzeichen) aus dem Dateinamen und überprüft dann, ob im Dateinamen entweder ein Bindestrich oder ein Komma verbleibt. Wenn einer übrig bleibt, gibt die Formel True zurück.

Sie können eine bedingte Formatierungsregel einrichten, um die Formel folgendermaßen zu verwenden:

  1. Wählen Sie die Zellen aus, die alle Dateinamen enthalten, die überprüft werden sollen.

  2. Klicken Sie auf der Registerkarte Start des Menübands in der Gruppe Stile auf die Option Bedingte Formatierung. Excel zeigt eine Palette von Optionen für die bedingte Formatierung an.

  3. Wählen Sie Zellenregeln hervorheben und dann im daraufhin angezeigten Untermenü Weitere Regeln. Excel zeigt das Dialogfeld Neue Formatierungsregel an.

(Siehe Abbildung 1.)

  1. Wählen Sie im Bereich Regeltyp auswählen oben im Dialogfeld die Option Formel verwenden, um zu bestimmen, welche Zellen formatiert werden sollen.

  2. Geben Sie im Feld Werte formatieren, in denen diese Formel wahr ist, die bereits besprochene lange Formel ein.

  3. Klicken Sie auf Formatieren, um das Dialogfeld Zellen formatieren anzuzeigen.

  4. Geben Sie mithilfe der Steuerelemente im Dialogfeld ein Format an, mit dem die Zellen hervorgehoben werden sollen, die gegen Ihr Muster verstoßen.

  5. Klicken Sie auf OK, um das Dialogfeld Zellen formatieren zu schließen. Die in Schritt 7 angegebene Formatierung sollte jetzt im Vorschaubereich für die Regel angezeigt werden.

  6. OK klicken.

Wenn die in Schritt 1 ausgewählten Zellen nicht mit Zelle A1 begonnen haben, müssen Sie die in Schritt 5 verwendete Formel ändern, um Ihre Anfangszelle wiederzugeben. (Alle drei Instanzen von A1 in der Formel müssten geändert werden, um auf Ihre Anfangszelle zu verweisen.)

Es gibt zwei große „Fallstricke“ bei der Verwendung dieser Formel in Ihrer bedingten Formatierungsregel. Erstens erkennt es keine doppelten Leerzeichen. Wenn der Dateiname beispielsweise „Leerzeichen, Leerzeichen, Bindestrich, Leerzeichen“ enthält, würde dies eine Verletzung des Musters darstellen. Die SUBSTITUTE-Funktion in der Formel würde jedoch das „Leerzeichen, Bindestrich, Leerzeichen“ entfernen und den zusätzlichen Leerzeichen in der resultierenden Zeichenfolge belassen. Dieser einzelne Raum würde nicht als Verletzung des Musters erkannt, obwohl dies der Fall ist.

Die Lösung hierfür wäre eine viel längere Formel oder die Umgehung der bedingten Formatierungsroute insgesamt und die Verwendung von Hilfsspalten. Dies speist sich direkt in das zweite „Gotcha“ ein, und es ist ein großes:

Wenn Sie zehntausend Zeilen mit einer bedingten Formatierung versehen (oder Hilfsspalten mit Formeln hinzufügen), werden Sie feststellen, dass die Neuberechnung Ihres Arbeitsblatts deutlich länger dauert. Daran führt kein Weg vorbei, wenn Sie dem Arbeitsblatt so viele Formeln hinzufügen.

Aus diesem Grund ist es möglicherweise besser, ein Makro zu entwickeln, das die Zellen hervorhebt. Das Makro kann dann manuell ausgeführt werden, wenn Sie die Muster überprüfen möchten. Dies bedeutet, dass Ihre normale Neuberechnung des Arbeitsblatts nicht verlangsamt wird.

Das folgende Makro kann in einem ausgewählten Zellenbereich ausgeführt werden.

Es wird überprüft, ob vor einem Bindestrich keine zwei Leerzeichen, nach einem Bindestrich zwei Leerzeichen, vor einem Komma ein Leerzeichen oder nach einem Komma zwei Leerzeichen stehen. Anschließend werden alle korrekt gemusterten Bindestriche und Kommas aus dem Dateinamen entfernt und überprüft, ob noch Bindestriche oder Kommas vorhanden sind. Wenn eine Verletzung einer dieser Bedingungen festgestellt wird, wird die Zelle mit Gelb formatiert.

Sub CheckFilenames1()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If Instr(sTemp1, "  -") > 0 Then bBad = True         If Instr(sTemp1, "-  ") > 0 Then bBad = True         If Instr(sTemp1, " ,") > 0 Then bBad = True         If Instr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If Instr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If Instr(sTemp2, ",") > 0 Then bBad = True

If bBad Then             c.Interior.Color = vbYellow         Else             c.Interior.Color = xlColorIndexNone         End If     Next c End Sub

Die Ausführung des Makros kann eine Weile dauern, muss jedoch erneut nur ausgeführt werden, wenn Sie die Feldnamen überprüfen möchten. Wenn Sie nicht möchten, dass das Makro die Zellenformatierung „durcheinander bringt“, möchten Sie möglicherweise eine Version, die Text in die Spalte rechts von Dateinamen einfügt, die gegen das gewünschte Muster verstoßen.

Sub CheckFilenames2()

Dim bBad As Boolean     Dim c As Range     Dim sTemp1 As String     Dim sTemp2 As String

For Each c In Selection         bBad = False         sTemp1 = c.Text

If InStr(sTemp1, "  -") > 0 Then bBad = True         If InStr(sTemp1, "-  ") > 0 Then bBad = True         If InStr(sTemp1, " ,") > 0 Then bBad = True         If InStr(sTemp1, ",  ") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, " - ", "")

If InStr(sTemp2, "-") > 0 Then bBad = True         sTemp2 = Replace(sTemp1, ", ", "")

If InStr(sTemp2, ",") > 0 Then bBad = True

If bBad Then c.Offset(0, 1) = "BAD"

Next c End Sub

Bei der Ausführung fügt diese Variation des Makros den Text „BAD“ in die Zelle rechts von falsch geordneten Dateinamen ein. Sie können dann die Filterfunktionen von Excel verwenden, um nur die Zeilen anzuzeigen, die den Text enthalten.

Natürlich möchten Sie dies alles nur einen Schritt weiter gehen lassen und dem Makro erlauben, falsch formatierte Dateinamen zu ändern. Das folgende Makro funktioniert für alle ausgewählten Zellen. Es stellt sicher, dass jeder Bindestrich von einem einzelnen Leerzeichen umgeben ist und auf jedes Komma nur ein einzelnes Leerzeichen folgt.

Sub FixFilenames()

Dim myArry() As String     Dim sTemp As String     Dim c As Range     Dim s As Variant

For Each c In Selection         myArry = Split(c, "-")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & " - " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         myArry = Split(sTemp, ",")

sTemp = ""

For Each s In myArry             If sTemp > "" Then                 sTemp = sTemp & ", " & Trim(s)

Else                 sTemp = Trim(s)

End If         Next s         c = sTemp     Next c End Sub

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

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