GLÄTTEN-Funktion – Beispiele in Excel, VBA und Google Sheets
Download the example workbook
Dieses Tutorial zeigt Ihnen, wie Sie die GLÄTTEN-Funktion in Excel, Google Sheets und VBA verwenden können, um alle überschüssigen Leerzeichen aus einem Text zu entfernen.
Verwendung der GLÄTTEN-Funktion:
Die GLÄTTEN-Funktion entfernt alle Leerzeichen in einem Text mit Ausnahme von einzelnen Leerzeichen zwischen Wörtern.
=GLÄTTEN(B3)
GLÄTTEN zum Entfernen von führenden und nachfolgenden Leerzeichen
Es kann schwierig sein, Zellen mit nachfolgenden Leerzeichen zu erkennen, da Text normalerweise linksbündig ist:
Wenn Sie jedoch auf die Zelle B3 oder B4 doppelklicken, können Sie die zusätzlichen Leerzeichen durch Hervorheben erkennen:
Anzahl der zusätzlichen Leerzeichen ermitteln
Vielleicht möchten Sie herausfinden, ob die Zelle zusätzliche Leerzeichen enthält, anstatt gleich GLÄTTEN anzuwenden.
=LÄNGE(B3)-LÄNGE(GLÄTTEN(B3))
Die LÄNGE-Funktion gibt die Gesamtzahl der Zeichen in einer Zelle zurück. Davon können Sie die Gesamtlänge des getrimmten Textes (bei dem alle zusätzlichen Leerzeichen entfernt wurden) abziehen.
Wenn GLÄTTEN nicht funktioniert
Vielleicht sehen Sie offensichtliche zusätzliche Leerzeichen in den Zellen, aber die Anwendung von GLÄTTEN scheint nicht zu funktionieren.
Das liegt daran, dass es sich nicht um typische Leerzeichen handelt. Alle unsere Zeichen auf der Tastatur haben einen bestimmten ASCII-Code (American Standard Code for Information Interchange). Sie können die CODE-Funktion verwenden, um den ASCII-Code eines Zeichens herauszufinden. Zum Beispiel:
=CODE(B3)
Das letzte ist ein normales Leerzeichen auf der Tastatur und sein ASCII-Code ist 32. Wenn Sie eines der „Leerzeichen“ von dort kopieren, wo GLÄTTEN nicht funktioniert und CODE darauf anwenden, wird 160 angezeigt:
Das ASCII-Zeichen 160 ist ein nicht umbrechendes Leerzeichen und wird normalerweise in Websites verwendet und in Excel kopiert. GLÄTTEN entfernt nur das Zeichen 32 (normale Leerzeichen).
Um die nicht umbrechenden Leerzeichen und die regulären Leerzeichen zu entfernen, müssen Sie die Funktionen WECHSELN und GLÄTTEN kombinieren.
=GLÄTTEN(WECHSELN(B3;ZEICHEN(160);""))
WECHSELN ersetzt hier alle nicht umbrechenden Leerzeichen (ASCII-Code 160) durch Leerzeichen und GLÄTTEN entfernt alle zusätzlichen regulären Leerzeichen (ASCII-Code 32), falls vorhanden.
GLÄTTEN mit Zahlen/Daten
GLÄTTEN ist eine Textfunktion. Wenn Sie GLÄTTEN verwenden, ist das Ergebnis ein Text. Zum Beispiel können Sie diese Zahlen in Zelle E3 nach der Anwendung von GLÄTTEN nicht mehr summieren.
Um die oben genannten Probleme zu lösen, können Sie die WERT-Funktion verwenden, um Text in Zahlenwerte umzuwandeln.
GLÄTTEN vs SÄUBERN
Die GLÄTTEN-Funktion funktioniert nicht bei nicht druckbaren Zeichen. Wenn Sie also nicht druckbare Zeichen entfernen müssen, verwenden Sie die SÄUBERN-Funktion. In der nachstehenden Tabelle finden Sie einen Vergleich der beiden Funktionen:
Nützliche Beispiele für die GLÄTTEN-Funktion
Sie können GLÄTTEN mit einigen der Excel-Funktionen auf nützliche Weise kombinieren.
Letztes Wort aus einer Zeichenkette extrahieren
Wir können RECHTS, WIEDERHOLEN und WECHSELN mit GLÄTTEN kombinieren, um das letzte Wort aus einer Zeichenkette zu extrahieren:
=GLÄTTEN(RECHTS(WECHSELN(A2;" ";WIEDERHOLEN(" ";10));10))Das würde zu folgenden Ergebnissen führen:
N-tes Wort in der Zeichenkette finden
Wir können TEIL, LÄNGE, WECHSELN und WIEDERHOLEN mit GLÄTTEN kombinieren, um das n-te Wort der Zeichenkette zurückzugeben.
=GLÄTTEN(TEIL(WECHSELN(A$2;" ";WIEDERHOLEN(" ";LÄNGE(A$2))); (B2-1)*LÄNGE(A$2)+1; LÄNGE(A$2)))
GLÄTTEN in Google Sheets
Die GLÄTTEN-Funktion funktioniert in Google Sheets genau so wie in Excel:
Zusätzliche Hinweise
Wenn Sie mit Text arbeiten, ist es immer eine gute Idee, die GLÄTTEN-Funktion zu verwenden, um den Text zu bereinigen. Zusätzliche Leerzeichen können bei der Arbeit mit Funktionen wie XVERWEIS und VERGLEICH zu unerwünschten Fehlern führen.
Sie können die IDENTISCH-Funktion verwenden, um zu prüfen, ob zwei Testzeichenketten gleich sind. Denken Sie daran, dass die Funktionen IDENTISCH, WECHSELN und FINDEN zwischen Groß- und Kleinschreibung unterscheiden. Es ist also eine gute Idee, die Funktionen GROSS, KLEIN oder GROSS2 zu verwenden, um den gesamten Text in eine einheitliche Groß- und Kleinschreibung zu bringen.
GLÄTTEN-Beispiele in VBA
Sie können die GLÄTTEN-Funktion auch in VBA verwenden. Geben Sie Folgendes ein:
Application.WorksheetFunction.Trim(Text)Die Funktionsargumente (Text usw.) können Sie entweder direkt in die Funktion eingeben oder Variablen definieren, die Sie stattdessen verwenden.
