CLEAN() Funktion in Excel: Der beste Weg, um nicht druckbare Zeichen zu entfernen

Hast du schon einmal Daten in Excel importiert und festgestellt, dass seltsame, versteckte Zeichen deine Arbeit durcheinander bringen? Das sind nicht druckbare Zeichen, die Formatierungsprobleme verursachen und sogar Berechnungen ruinieren.  

Solche unerwünschten Zeichen kannst du mit der Funktion CLEAN() entfernen. In diesem Artikel erkläre ich dir die Funktion CLEAN(), wann du sie verwenden solltest und wie sie dir die Arbeit erleichtern kann.

Schnelle Antwort: Die Excel-Funktion CLEAN()

So benutzt du die Funktion CLEAN 

• Typ =CLEAN(.

• Gib entweder den Text ein, den du bereinigen willst, oder den Zellbezug.

• Schließe die Funktion mit ).

• Drücke eingeben.

=CLEAN(text)

text kann ein beliebiger String sein, den du reinigen willst.

=CLEAN(A1)

A1 kann durch einen anderen Zellbezug ersetzt werden.

Ein Beispiel für die Funktion CLEAN() in Aktion

Wir wollen nun etwas genauer verstehen, wie wir die Funktion CLEAN() nutzen können. Dieses Mal füge ich ein paar Screenshots hinzu.

Hier habe ich Daten in Zelle A2 mit einigen unerwünschten Zeichen. Ich gebe also die folgende Formel in Zelle B2 ein, um meinen Text zu bereinigen:

=CLEAN(A2)

Verwende die Funktion CLEAN() in einer einzelnen Zelle. Bild vom Autor.

Neben der Reinigung einer einzelnen Zelle können wir mit der Funktion CLEAN() sogar mehrere Zellen gleichzeitig reinigen. Alles, was du tun musst, ist, nachdem du die Formel in die entsprechende Zelle geschrieben hast, das kleine grüne Quadrat in der Ecke der Zelle in die letzte Zeile zu ziehen. Auf diese Weise wird die Formel auf den gesamten Datensatz angewendet, und du hast innerhalb von Sekunden die richtigen Daten. 

Bereinige mehrere Zellen mit der Funktion CLEAN(). Bild vom Autor.

Ich finde die Funktion CLEAN() besonders nützlich, nachdem ich Daten aus externen Quellen wie Datenbanken oder Webseiten importiert habe. Das liegt daran, dass beim Importieren von Daten auch nicht druckbare Zeichen mit den ASCII-Codes 0-31 vorkommen können. Auch wenn wir sie nicht sehen können, können sie unsere Berechnungen durcheinander bringen oder unsere Daten seltsam aussehen lassen. 

Mit der Funktion CLEAN() von Excel werden diese unerwünschten Zeichen entfernt. Es bereinigt sogar unnötige Zeilenumbrüche und andere versteckte Zeichen, die Formatierungsprobleme verursachen können. Deshalb ist es manchmal hilfreich, CLEAN() zu nutzen, auch wenn du keine offensichtlichen Probleme siehst.

Excel CLEAN() vs. TRIM()

Wenn du einen unserer Kurse, wie z. B. Excel Grundlagen, besucht hast, bist du vielleicht mit einer anderen gängigen Excel-Funktion vertraut , der TRIM()-Funktion.

Die Funktionen CLEAN() und TRIM() bereinigen zwar beide den Text, funktionieren aber unterschiedlich. CLEAN() entfernt nicht druckbare Zeichen (ASCII 0-31) und TRIM() entfernt zusätzliche Leerzeichen (ASCII 32) vor, nach oder zwischen Wörtern (außer praktischerweise einem einzelnen Leerzeichen zwischen den Wörtern). 

Mit anderen Worten: Wir können CLEAN() verwenden, um Daten mit versteckten Formatierungsproblemen zu bereinigen, aber wenn unsere Daten inkonsistente Abstände aufweisen, können wir dies mit der Funktion TRIM() beheben.

Später zeige ich dir, wie du CLEAN() mit TRIM() kombinierst, um sowohl nicht druckbare Zeichen als auch zusätzliche Leerzeichen zu entfernen.

Kombinieren von CLEAN() mit anderen Excel-Funktionen 

Jetzt, wo du weißt, was die Funktion CLEAN() ist, wollen wir sehen, wo wir sie in der Praxis einsetzen können:

Kombination von CLEAN() mit TRIM() 

Wie du im obigen Beispiel siehst, hat CLEAN() die seltsamen Abstände zwischen den Texten nicht entfernt. Um dies zu beheben, schließe ich sie in die Funktion TRIM() ein und du kannst sehen, dass die Daten in der Spalte B nun richtig formatiert sind:

=TRIM(CLEAN(A2))

Kombiniere die Funktionen TRIM() und CLEAN(). Bild vom Autor.

Verwendung von CLEAN() mit SUBSTITUTE()

Neben den ersten 32 ASCII-Zeichen gibt es einige nicht druckbare Unicode-Zeichen, die CLEAN() allein nicht verarbeiten kann. Aber wenn wir die Funktion SUBSTITUTE() mit CLEAN(), kombinieren, können wir sie leicht entfernen. Lass uns das anhand eines Beispiels verstehen:

Ich habe einen zufälligen Text in Zelle A2 , bei dem es auf der linken Seite des Textes ein Problem mit dem Abstand gibt. Aber als ich versucht habe, sie mit TRIM() zu entfernen, ging sie nicht weg. Das heißt, es sieht aus wie ein Leerzeichen, ist es aber nicht - stattdessen ist es ein Sonderzeichen. In solchen Fällen ermittelst du den Zeichencode zunächst mit der folgenden Formel:

=CODE(LEFT(A2,1))

Dies ergibt den Zeichencode, der 160 lautet. Du kannst sie nun mit der folgenden Formel entfernen:

=SUBSTITUTE(CLEAN(A2),CHAR(160),"")

Um den Text übersichtlicher zu gestalten, schließe die obige Formel in TRIM() ein.

=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160),""))

Bereinige Zellen mit CLEAN() und SUBSTITUTE(). Bild vom Autor.

Verwendung von CLEAN() mit REPLACE()

Wenn wir ein anderes Trennzeichen durch nicht druckbare Zeichen ersetzen wollen, können wir die Funktion CLEAN() mit der Funktion REPLACE() kombinieren. Die Funktion REPLACE() in Excel ersetzt einen Teil eines Textes durch einen neuen Text, basierend auf einer bestimmten Startposition und Länge. 

=REPLACE(old_text, start_num, num_chars, new_text)

Wenn ich zum Beispiel das Zeichen - durch ein Leerzeichen ersetzen und alle nicht druckbaren Zeichen entfernen möchte, verwende ich die folgende Formel:

=REPLACE(CLEAN(A2), 4, 1, " ")

Diese Formel bereinigt zunächst den alten Text und ersetzt - an Position 4 durch ein Leerzeichen.

Bereinige den Text mit den Funktionen REPLACE() und CLEAN(). Bild vom Autor.

Verwendung von CLEAN() mit REPT()

Wir können auch die Funktion REPT() mit CLEAN() kombinieren, um den Text zu bereinigen. Mit der Funktion REPT() wird ein Text eine bestimmte Anzahl von Malen wiederholt. 

=REPT(text, number_of_times)

Ich wende zum Beispiel die folgende Formel auf meinen Datensatz an: 

=TRIM(CLEAN(SUBSTITUTE(A2, " ", REPT(" ", 2))))

Hier ersetzt SUBSTITUTE() mit REPT(" ", 2) jedes einzelne Leerzeichen durch zwei Leerzeichen, um die Abstände einheitlicher zu machen. Dann entfernt CLEAN() die versteckten nicht druckbaren Zeichen und TRIM() die zusätzlichen Leerzeichen, sodass nur ein Leerzeichen zwischen den Wörtern bleibt.

Bereinige den Text mit REPT() und CLEAN(). Bild vom Autor.

Wie du siehst, taucht CLEAN() an vielen Stellen auf und funktioniert gut mit vielen anderen Funktionen.

Ein paar Dinge zu beachten

CLEAN() und SUBSTITUTE() sind gut geeignet, um unerwünschte Zeichen zu entfernen, aber manchmal kann es passieren, dass wir etwas Wichtiges entfernen, wenn wir nicht aufpassen. Zum Beispiel: 

• Versehentlicher Datenverlust: Vermeide es, zu weit gefasste Formeln zu verwenden. Wenn wir alle Leerzeichen durch nichts ersetzen, können die Wörter zusammengeführt werden. Und wenn wir alle Sonderzeichen entfernen, kann unsere Formel Bindestriche aus Telefonnummern oder Dezimalpunkte aus Preisen löschen. Bevor du also eine Funktion anwendest, prüfe, ob die Formel richtig funktioniert. 
• Test auf Teilmengen: Um sicherzugehen, teste ich meine Formel immer zuerst an einem kleinen Datensatz. Anstatt es auf Tausende von Zeilen auf einmal anzuwenden, probiere ich es an einigen wenigen aus und prüfe, ob es so funktioniert, wie ich es erwarte. Wenn alles gut aussieht, wende ich es erst dann auf den gesamten Datensatz an.

Schlussgedanken

Die Funktion CLEAN() bereinigt Textdaten schnell, indem sie nicht druckbare Zeichen entfernt. Wenn es allein verwendet wird, sorgt es für saubere und strukturierte Daten. In Kombination mit der Funktion TRIM() verbessert sie die Lesbarkeit, indem sie auch unnötige Leerzeichen entfernt.

Wenn du noch mehr Excel-Tipps und -Tricks lernen willst, haben wir einige tolle Kurse für dich. Beginne mit dem Lernpfad Excel-Grundlagen, um die Grundlagen zu erlernen, und fahre dann mit den Kursen Datenanalyse in Excel und Datenaufbereitung in Excel fort, um deine Kenntnisse zu vertiefen.