Leerzeichen entfernen in Excel: GLÄTTEN, SÄUBERN, WECHSELN

=GLÄTTEN(A2) entfernt alle Leerzeichen vor und nach dem Text in A2 und kürzt jede Folge von Leerzeichen zwischen Wörtern auf ein einziges. Aus " Ana Silva " wird Ana Silva. GLÄTTEN heißt in englischem Excel TRIM, und die Tabelle zeigt die englische Schreibweise, =TRIM(A2). Du kannst die Formeln in der Tabelle auch deutsch eingeben, mit Semikolons: =GLÄTTEN(A2).

FormelnZurücksetzen

B2fx

=TRIM(A2)

	A	B	C	D
1	Name	TRIM	Length before	Length after
2	Ana Silva	Ana Silva	14	9
3	Ben Okafor	Ben Okafor	13	10
4	Chen Wu	Chen Wu	10	7

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =GLÄTTEN(A2)

In Spalte A sind die Leerzeichen unsichtbar, deshalb gibt es die Spalten mit der Länge: LÄNGE (englisch LEN) zählt sie mit. Überzählige Leerzeichen kommen meist mit importierten oder eingefügten Daten, und sie stören Suchen und Vergleiche, ohne dass man etwas sieht.

Warum überzählige Leerzeichen Formeln stören

Für Excel sind Ana mit einem Leerzeichen am Ende und Ana verschiedene Texte. Ein Vergleich liefert FALSCH, ZÄHLENWENN zählt die Zelle nicht, und ein Verweis liefert #NV (englisch #N/A):

FormelnZurücksetzen

D3fx

=XLOOKUP("Ana",TRIM(A2:A4),B2:B4,"not found")

	A	B	C	D
1	Name	Score	Is it Ana?	Ana's score
2	Ana	90	FALSE	not found
3	Ben	85	TRUE	90
4	Chen	78

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =XVERWEIS("Ana";GLÄTTEN(A2:A4);B2:B4;"not found")

C2 ist FALSE (FALSCH), und D2 findet nichts (ohne sein viertes Argument würde XVERWEIS #NV zeigen; die Tabelle zeigt die englischen Namen), weil in A2 Ana mit Leerzeichen steht. D3 glättet die ganze Suchspalte innerhalb der Formel und findet 90. Das funktioniert in Microsoft 365 und Excel 2021; in älteren Versionen bereinigst du die Spalte vorher. Leerzeichen sind eine der häufigsten Ursachen für #NV bei einem Verweis.

Alle Leerzeichen entfernen

GLÄTTEN behält immer ein Leerzeichen zwischen den Wörtern. Um jedes Leerzeichen zu entfernen, etwa aus einer Telefonnummer oder einem Produktcode, ersetzt du das Leerzeichen durch nichts:

FormelnZurücksetzen

B2fx

=TRIM(A2)

	A	B	C
1	Code	TRIM	No spaces
2	AB 12 34	AB 12 34	AB1234
3	555 201 3344	555 201 3344	5552013344

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =GLÄTTEN(A2)

FormelnZurücksetzen

B2fx

	A	B
1	Customer	Clean name
2	Eli Novak
3	Fay Ruiz
4	Gus Lee

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.

Du bist dran: Die Namen in Spalte A haben überzählige Leerzeichen. Gib in B2 den Namen aus A2 ohne Leerzeichen davor und danach und mit einfachen Leerzeichen zwischen den Wörtern zurück. Die Formel wird bis B4 nach unten ausgefüllt.

PrüfenLösung zeigen

GLÄTTEN funktioniert nicht: geschützte Leerzeichen

Text, der aus einer Webseite oder einem PDF kopiert wurde, enthält oft geschützte Leerzeichen, Zeichen 160, statt normaler Leerzeichen, Zeichen 32. Sie sehen gleich aus, aber GLÄTTEN entfernt nur Zeichen 32, also ändert sich in Excel nichts:

A2:             Ana Silva   (a non-breaking space between the names and one at the end)
=LEN(A2)        10
=LEN(TRIM(A2))  10, TRIM removed nothing

Die Lösung: Mach mit WECHSELN zuerst aus jedem Zeichen 160 ein normales Leerzeichen und lass GLÄTTEN den Rest erledigen. Der Text in A2 unten ist mit ZEICHEN(160) gebaut, damit du das Zeichen siehst, das eine Webseite einfügt:

FormelnZurücksetzen

D2fx

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

	A	B	C	D	E
1	Pasted text	Length	Code of 4th character	Fixed	Fixed length
2	Ana Silva	10	160	Ana Silva	9

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);" "))

In deutschem Excel lautet die Formel in D2 =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);" ")). C2 zeigt, wie du ein seltsames Zeichen erkennst: CODE(TEIL(A2;4;1)) liefert den Code des 4. Zeichens, hier 160. D2 hat 9 Zeichen, die 10 aus A2 minus das Leerzeichen am Ende.

SÄUBERN: Zeilenumbrüche und andere versteckte Zeichen entfernen

SÄUBERN (englisch CLEAN) entfernt die nicht druckbaren Zeichen mit den Codes 0 bis 31: Zeilenumbrüche (10), Wagenrückläufe (13) und Tabulatoren (9). Leerzeichen rührt es nicht an, also ist das übliche Paar =GLÄTTEN(SÄUBERN(A2)):

FormelnZurücksetzen

B2fx

=CLEAN(A2)

	A	B	C
1	Imported	CLEAN	TRIM(CLEAN)
2	Ana Silva	AnaSilva	AnaSilva

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =SÄUBERN(A2)

SÄUBERN löscht den Zeilenumbruch einfach, also laufen die beiden Namen als AnaSilva zusammen. Trennt ein Zeilenumbruch Wörter, ersetzt du ihn stattdessen durch ein Leerzeichen, =GLÄTTEN(WECHSELN(A2;ZEICHEN(10);" ")); die Seite zum Zeilenumbruch zeigt diesen Fall.

Die ursprüngliche Spalte durch geglätteten Text ersetzen

GLÄTTEN schreibt sein Ergebnis in eine andere Zelle. Um die Daten selbst zu reparieren:

1. Schreib =GLÄTTEN(A2) in eine leere Spalte neben den Daten und füll sie nach unten aus.
2. Kopiere diese Spalte (Strg+C, auf dem Mac Cmd+C).
3. Markiere die ursprüngliche Spalte und wähle Start > Einfügen > Werte einfügen, damit die Zellen Text bekommen, keine Formeln.
4. Lösch die Hilfsspalte.

Suchen und Ersetzen kann Leerzeichen ohne Formel entfernen, unterscheidet aber Leerzeichen am Anfang nicht von denen zwischen Wörtern: Ersetzt du ein Leerzeichen durch nichts, wird aus Ana Silva der Text AnaSilva. Zwei Leerzeichen durch eins ersetzen, so oft, bis Excel nichts mehr zu ersetzen findet, ist die Handarbeit-Version der Regel, die GLÄTTEN für Leerzeichen zwischen Wörtern anwendet. Ein einzelnes Leerzeichen am Anfang oder Ende bleibt stehen.