Ich stolpere immer mal wieder drüber und dir ist es sicherlich auch schon passiert:
Beim Abgleich von Daten wird nichts gefunden, obwohl der gesuchte Wert ganz offensichtlich in der Tabelle vorhanden ist. Oder: In einer Pivot-Tabelle taucht ein Artikel oder ein Name zweimal auf, und das bei gleicher Schreibweise.
Die Ursache ist banal und doch so schwer zu erkennen: Ein saudummes Leerzeichen!
Was für den menschlichen Betrachter keinen Unterschied macht, kann in Excel zu unerwarteten Problemen führen. Wie man damit umgeht und wie man diese kleinen Teufel in seinen Daten findet, erkläre ich in diesem Artikel.
Und so geht’s:
Das Problem
Aus den unterschiedlichsten Gründen (ja, meistens sitzt das Problem vor dem Computer, aber nicht immer) kommt es vor, dass sich am Ende eines Artikels, Namens oder sonstigen Textes ein unnötiges Leerzeichen angehängt hat.
Statt "Müller" steht dort dann eben "Müller " (mit Leerzeichen am Ende) oder " Müller" (mit Leerzeichen am Anfang). Für Excel sind das aber lauter unterschiedliche Namen.
Die Ursache kann sein, dass Daten aus einem anderen System importiert wurden und dabei schon unnötige Leerzeichen mitgeliefert wurden. Oder ein Anwender hat beim Kopieren eines Namens oder einer Artikelnummer ein Leerzeichen mit markiert und dann eingefügt.
Und damit hat man sich unwissentlich einen Saboteur ins Nest gelegt…
Wenn man Listen beispielsweise mit einem XVERWEIS gegeneinander abgleicht, dann sucht Excel in aller Regel nach einer exakten Übereinstimmung. Und exakt bedeutet eben exakt. Als Anwender wird man dann mit einem #NV-Fehler „belohnt“, obwohl man mit eigenen Augen sieht, dass der gesuchte Artikel vorhanden ist:
=XVERWEIS(B3;$F$3:$F$7;$G$3:$G$7)
XVERWEIS liefert einen #NV-Fehler
Gründe für den #NV-Fehler und alle anderen Fehlerarten habe ich in diesen beiden Artikeln beschrieben: Excel-Fehler entschlüsselt: Das steckt hinter #DIV/0!, #WERT! & Co. (Teil 1) und
Excel-Fehler entschlüsselt: Das steckt hinter #DIV/0!, #WERT! & Co. (Teil 2)
Auch wenn man beide Listen in Power Query mit einem Join zusammenführt, wird für einen Artikel kein Preis gefunden.
Ähnlich ärgerlich sieht es dann aus, wenn man aus der Rechnungsliste eine Pivot-Tabelle erstellt. Der Artikel „Carbonic“ erscheint doppelt:
Die Pivot-Tabelle zeigt doppelte Einträge
Die Liste mit potenziellen Problemen ließe sich noch lange weiterführen. Und die Ursache ist immer das für unser Auge nicht erkennbare angehängte Leerzeichen.
Klickt man in der betreffenden Zelle oben in die Bearbeitungszeile, dann sieht man, dass der Cursor nicht direkt hinter dem Text, sondern erst nach einem Leerzeichen blinkt:
Das Leerzeichen ist in der Bearbeitungszeile erkennbar
Wie lässt sich dieses Ärgernis aber generell lösen?
Je nach gewünschtem Ergebnis sind verschiedene Ansätze möglich:
Also, ob das Leerzeichen nur sichtbar gemacht werden oder ob es direkt gleich entfernt werden soll.
Leerzeichen sichtbar machen
Vielleicht möchtest du ja wissen, an welchen Stellen ein solches Leerzeichen existiert. Dann kann man es auch in etwaigen anderen Quellsystemen gleich bereinigen.
Möglichkeit 1: Zellenausrichtung
Diesen kleinen aber genialen Trick habe ich bei meinem Excel-Kollegen Rene Martin gesehen. Auf seinem Blog mit dem „Excel nervt – Eine Liebeserklärung an MS Excel“ thematisiert er humorvoll kleinere und größere Unzulänglichkeiten unserer geliebten Tabellenkalkulation.
Dazu markiert man einfach die Spalte, die auf unnötige Leerzeichen hin überprüft werden soll und ändert die Textausrichtung auf „rechtsbündig“. Schon wird der Übeltäter sichtbar:
Textausrichtung macht Leerzeichen sichtbar
Diese Methode eignet sich natürlich nur für überschaubare Datenbestände, aber es ist ein sehr schneller und einfacher Weg.
Möglichkeit 2: Textlänge überprüfen
Mit Hilfe der beiden Funktionen LÄNGE und GLÄTTEN lässt sich in einer zusätzlichen Spalte prüfen und anzeigen, ob eine Zelle unnötige Leerzeichen enthält:
=LÄNGE(B3)<>LÄNGE(GLÄTTEN(B3))
Der Längenvergleich zeigt den Unterschied
Die GLÄTTEN-Funktion entfernt sämtliche Leerzeichen am Anfang, Ende und auch in der Mitte eines Textes, sofern sie unnötig sind. „Unnötig“ in diesem Zusammenhang bedeutet in der Textmitte 2 oder mehr aufeinander folgende Leerzeichen.
Möglichkeit 3: Die REGEXTESTEN-Funktion (nur M365)
Diese Funktion gehört zu einer Gruppe von insgesamt 3 neuen REGEX-Funktionen, die nur Microsoft 365-Anwendern zu Verfügung stehen. Ich habe diese Funktionen kürzlich in einem eigenen Artikel vorgestellt.
Für unseren Anwendungsfall würde die Formel so aussehen:
=REGEXTESTEN(B3;"\s$")
Leerzeichen mit REGEXTESTEN erkennen
Das \s steht für einen sogenannten Whitespace, das sind eben Leerzeichen oder sonstige nicht druckbare Zeichen. Das $ bedeutet, dass nur am Ende der Textkette gesucht werden soll.
Leerzeichen am Textbeginn würde man mit folgender Funktion erkennen:
=REGEXTESTEN(B14;"^\s")
Möglichkeit 4: Bedingte Formatierung
Wer keine zusätzliche Spalte einfügen möchte, kann stattdessen mit der gleichen Technik wie in Möglichkeit 2 eine bedingte Formatierungsregel anlegen:
Texte mit Leerzeichen mit bedingter Formatierung hervorheben
Diese beiden Methoden (Formeln in einer Hilfsspalte und bedingte Formatierung) haben den Vorteil, dass man die Tabellen auch nach dem WAHR/FALSCH-Wert bzw. nach der Farbe sortieren oder filtern kann.
Möglichkeit 5: Power Query
Wer seine Daten mit Power Query importiert und dabei etwaige Leerzeichen gleich sichtbar machen möchte, kann diese natürlich auch in Power Query tun. Beispielsweise über eine benutzerdefinierte Spalte mit folgender M-Funktion:
=Text.Length([Produkt]) = Text.Length(Text.Trim([Produkt]))
Längenvergleich in Power Query
Die neue Spalte enthält damit TRUE bzw. FALSE-Werte, abhängig davon, ob eben ein unnötiges Leerzeichen enthalten ist oder nicht.
Leerzeichen entfernen
Wie wird man nun diese kleinen Saboteure wieder los? Auch dafür gibt es je nach Gegebenheiten unterschiedliche Ansatzpunkte.
Möglichkeit 1: Die GLÄTTEN-Funktion
Wie schon oben beschrieben, entfernt diese Funktion führende, angefügte oder im Text enthaltene unnötige Leerzeichen:
=GLÄTTEN(B3)
Bereinigen mit der GLÄTTEN-Funktion
Anschließend kopiert man diese Hilfsspalte mit Strg+C in die Zwischenablage. Über einen Rechtsklick lassen sich nun nur die Werte einfügen und man erhält saubere Texte:
Keine Leerzeichen in den eingefügten Texten
Möglichkeit 2: Die SÄUBERN-Funktion
Diese weniger bekannte Funktion entfernt nicht nur Leerzeichen, sondern auch andere nicht druckbare Zeichen (technisch gesprochen sind das die ersten 32 nicht druckbaren Zeichen des 7-Bit-ASCII-Codes)
=SÄUBERN(B3)
Bereinigen mit der SÄUBERN-Funktion
Möglichkeit 3: Die REGEXERSETZEN-Funktion (nur M365)
Ähnlich wie bei Suchen & Ersetzen kann mit dieser Funktion nach einem beliebig komplexen Muster gesucht und durch ein anderes Muster ersetzt werden. In unserem Fall reicht ein einfaches Muster aus:
=REGEXERSETZEN(B3;"\s$";"")
Bereinigen mit der REGEXERSETZEN-Funktion
Das heißt, das Leerzeichen wird durch „Nichts“ ersetzt. Auch hier gilt wieder für ein Leerzeichen am Textanfang:
=REGEXERSETZEN(B3;"^\s;"")Ansonsten sei an dieser Stelle wieder auf meinen REGEX-Artikel verwiesen.
Möglichkeit 4: Power Query
Die komfortabelste Methode insbesondere für große und/oder importierte Datenbestände liefert natürlich Power Query. Über einen Rechtsklick auf den betreffenden Spaltenkopf lassen sich folgende beiden alternativen Optionen verwenden:
Kürzen oder Bereinigen
Leerzeichen mit Power Query entfernen
Sowohl bei Kürzen (= Text.Trim) als auch bei Bereinigen (= Text.Clean) verschwinden die Leerzeichen, letztere Funktion entfernt auch andere Steuerzeichen, wie zum Beispiel einen Zeilenumbruch.
Fazit
Unerwünschte Leerzeichen sind ein großes Ärgernis in Excel. Die Gründe, warum sie in deinen Daten auftauchen können, sind ganz unterschiedlich. Zuverlässig verhindern lassen sie sich deshalb nur schwer. Klar ist aber:
Sie sabotieren deine Auswertungen und führen häufig zu falschen Ergebnissen, bei denen das Problem nicht immer sofort erkennbar ist. Kleine Ursache, große Wirkung.
In dem Artikel habe ich dir eine Reihe von Möglichkeiten gezeigt, wie du solche Störenfriede erkennen und beseitigen kannst. Je nach Anwendungsfall kannst du dir nun das passende Werkzeug aussuchen.
Kennst du noch weitere Methoden, um Leerzeichen zu erkennen? Habe ich irgendeinen wichtigen Aspekt übersehen? Dann lass es uns in den Kommentaren wissen!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
SÄUBERN() entfernt Leerzeichen nicht! Denn sie beseitigt nur Zeichen mit den ASCII-Codes 0-31.