Wie man Leerzeichen in Excel entfernt

Ich hatte einmal eine Tabelle mit mehr als 500 Einträgen, die anfangs gut aussah, aber die Summen nicht korrekt berechnete. Ich wusste nicht, warum, aber später stellte sich heraus, dass die versteckten Räume alle möglichen Probleme verursachten. Wenn du mit Daten in Excel arbeitest, bist du vielleicht schon auf ähnliche Situationen gestoßen. Diese Lücken entstehen oft, wenn du Daten aus anderen Quellen importierst.  

In diesem Leitfaden zeige ich dir verschiedene Methoden, um unerwünschte Leerzeichen in Excel zu entfernen. Du bekommst für jede Methode eine Schritt-für-Schritt-Anleitung und praktische Beispiele, um dir die Kopfschmerzen zu ersparen, die ich durchgemacht habe. Wenn du jedoch gerade erst anfängst, solltest du dir unseren Kurs Datenanalyse in Excel oder den Lernpfad Excel-Grundlagen ansehen. 

Das Verständnis von Leerzeichen in Excel

Bevor wir Leerzeichen entfernen, sollten wir die Arten von Leerzeichen verstehen, die in deinen Daten lauern können:

• Führende Räume: Leerzeichen am Anfang einer Zelle.
• Nachfolgende Leerzeichen: Leerzeichen am Ende einer Zelle.
• Zusätzliche Leerzeichen zwischen den Wörtern: Mehr als ein Leerzeichen zwischen den Wörtern.
• Nicht-umgebende Leerzeichen: Besondere Räume, die normal aussehen, sich aber anders verhalten.

Diese Lücken können sich durch importierte Dateien, manuelle Eingabefehler, inkonsistente Formatierung oder sogar durch Kopieren und Einfügen aus anderen Quellen in deine Daten einschleichen.

Arten von Räumen. Bild vom Autor. 

Verschiedene Möglichkeiten, Leerzeichen in Excel zu entfernen

Auch wenn Lücken klein erscheinen, können sie große Probleme verursachen. Formeln könnten nicht funktionieren, Vergleiche könnten fehlschlagen und Berechnungen könnten falsch ausfallen. Bevor du dich auf Lösungen stürzt, empfehle ich Folgendes:

• Sichern Sie Ihre Daten: Mache immer eine Kopie deiner Daten, bevor du Änderungen vornimmst. So stellst du sicher, dass du die ursprünglichen Daten wiederherstellen kannst, wenn etwas schiefgeht.
• Überprüfe die Formatierung: Achte auf eine einheitliche Formatierung deiner Zellen, z. B. Groß- und Kleinschreibung und Zahlenformate, um saubere und zuverlässige Ergebnisse zu erzielen.

Jetzt wollen wir uns die besten Methoden zum Entfernen von Leerzeichen in Excel ansehen.

Methode 1: Verwende die Funktion TRIM(), um zusätzliche Leerzeichen zu entfernen

Die Funktion TRIM() entfernt unnötige Leerzeichen, wie z. B. führende, nachgestellte und mehrfache Leerzeichen zwischen Wörtern, mit Ausnahme des einfachen Leerzeichens. So verwendest du es:

1. Erstelle eine Hilfsspalte neben deinen Daten.

2. Gib in der ersten Zelle der Hilfsspalte die Formel ein: =TRIM(A2).

3. Drücke die Eingabetaste und ziehe die Formel nach unten, um sie auf alle Zeilen anzuwenden.

Verwende TRIM(), um die Leerzeichen zu entfernen. Bild vom Autor.

Methode 2: Verwende die Funktion SUBSTITUTE()

Die Funktion SUBSTITUTE() entfernt alle Leerzeichen (einschließlich der Leerzeichen zwischen Wörtern) aus einer Zelle und ersetzt alle Zeichen, einschließlich der Leerzeichen, durch etwas anderes. Sie bereinigt numerische Daten, indem sie zum Beispiel Leerzeichen zwischen den Ziffern entfernt. 

Die grundlegende Syntax lautet:

=SUBSTITUTE(text, old_text, new_text, [instance_num])

Hier erfährst du, wie du sie anwendest und alle Leerzeichen entfernst: 

1. Erstelle eine Hilfsspalte.

2. Gib die Formel ein: =SUBSTITUTE(A2," ","").

3. Drücke die Eingabetaste und ziehe sie nach unten, um sie auf alle Zeilen anzuwenden.

Benutze die Funktion SUBSTITUTE(). Bild vom Autor.

Methode 3: Datenbereinigung mit Suchen & Ersetzen

Wenn du eine schnelle und einfache Lösung suchst, empfehle ich dir die Excel-Funktion "Suchen & Ersetzen " - sie kann zusätzliche Leerzeichen zwischen Wörtern verarbeiten. Und so geht's:

1. Wähle deinen Datenbereich aus.

2. Drücke Ctrl+H, um den Dialog Suchen & Ersetzen zu öffnen.

3. Gib in das Feld Finde was zwei Leerzeichen ein.

4. In das Feld Ersetzen durch gibst du ein einzelnes Leerzeichen ein.

5. Klicke auf Alle ersetzen.

Räume mit der Funktion "Suchen & Ersetzen" reinigen. Bild vom Autor.

Wiederhole die Schritte, bis Excel die Meldung " Wir konnten nichts zum Ersetzen finden" anzeigt. Beachte, dass diese Methode führende und nachgestellte Leerzeichen auf ein einziges Leerzeichen reduziert, sie aber nicht vollständig entfernt. Es geht nur um die Leerzeichen zwischen den Wörtern. 

Methode 4: Verwende die Funktion CLEAN(), um nicht druckbare Zeichen zu entfernen

Wenn wir Daten aus externen Quellen importieren oder kopieren, erhalten wir oft nicht druckbare Zeichen, die den Datenbetrieb stören können. Du kannst die Funktion CLEAN() verwenden, um sie zu entfernen, oder - noch besser - sie mit TRIM() kombinieren, um überflüssige Leerstellen zu beseitigen.

So kannst du es tun: 

1. Gib die Formel: =TRIM(CLEAN(A2)) in eine Hilfsspalte ein.

2. Drücke die Eingabetaste und ziehe die Formel nach unten.

Kombiniere TRIM() und CLEAN(), um Leerzeichen zu entfernen. Bild vom Autor.

Methode 5: Fortgeschrittene Methode - verwende Excel Add-Ins für schnellere Ergebnisse

Du kannst das Drittanbieter-Add-in Ablebits Ultimate Suite für Excel verwenden. Das Tool Trim Spaces bereinigt die Daten schnell. Mit einem Klick ist alles erledigt. Dazu gehören führende und nachgestellte Leerzeichen, Zeilenumbrüche, zusätzliche Leerzeichen zwischen Wörtern und nicht umbrechende Leerzeichen. Wenn du also wie ich regelmäßig mit großen Datenmengen arbeitest, kannst du damit viel Zeit sparen. 

Hier erfährst du, wie du es verwenden kannst:

1. Wähle den Bereich der zu bereinigenden Zellen aus.

2. Öffne die Registerkarte Ablebits Data und klicke auf Trim Spaces.

3. Wähle im Optionsbereich aus, was du entfernen möchtest, und klicke auf Zuschneiden.

Trimme die Leerzeichen mit dem Excel-Add-in. Bild vom Autor.

Spezifische Lösungen für gängige Szenarien der Leerstandsbeseitigung in Excel

Sehen wir uns nun ein paar häufige Szenarien an, auf die du stoßen könntest, und wie du sie schnell beheben kannst.

Zusätzliche Leerzeichen entfernen

Zusätzliche Leerzeichen können dazu führen, dass deine Daten unordentlich aussehen und schwer zu bearbeiten sind. Du kannst die Funktion TRIM() verwenden, um sie zu bereinigen und dabei ein Leerzeichen zwischen den Wörtern beizubehalten. Ich habe zum Beispiel ein paar Dateneinträge mit uneinheitlichen Abständen. Um dies zu sortieren, habe ich die folgende Formel in Zelle B2 eingegeben und sie auf alle Zeilen angewendet:

=TRIM(A2)

Verwende TRIM(), um zusätzliche Leerzeichen zu entfernen. Bild vom Autor.

Führende Leerzeichen entfernen 

Manchmal müssen wir mehrere Leerzeichen zwischen den Wörtern beibehalten, um unsere Daten besser lesbar zu machen, aber wir entfernen alle Leerzeichen am Anfang. Die Funktion TRIM() funktioniert gut, entfernt aber alle Leerzeichen aus dem Text. Wenn du in einem solchen Fall nur die führenden Leerzeichen entfernen und den Rest beibehalten willst, kannst du diese erweiterte Formel verwenden:

=MID(A2,FIND(MID(TRIM(A2),1,1),A2),LEN(A2))

So funktioniert die Formel: Er findet das erste Zeichen, das kein Leerzeichen ist, und behält alles von diesem Punkt an. Auf diese Weise werden die Leerzeichen zwischen den Wörtern beibehalten und nur am Anfang der Zelle abgeschnitten (führende Leerzeichen).

Entferne nur führende Leerzeichen. Bild vom Autor.

Nachfolgende Leerzeichen entfernen

Hintere Leerzeichen sind zusätzliche Leerzeichen, die nach Text oder Zahlen in einer Zelle erscheinen. Diese unsichtbaren Zeichen können Probleme wie Formelfehler verursachen, bei denen Zellen, die identisch erscheinen, in Funktionen wie VLOOKUP() oder MATCH() nicht übereinstimmen. Um dies zu vermeiden, verwende die Funktion TRIM(). Ich habe zum Beispiel diese Formel in Zelle B2 eingegeben und sie dann auf alle Zeilen angewendet, um zusätzliche Leerzeichen aus meinen Daten zu entfernen.

=TRIM(A2)

Entfernen von Leerzeichen am Ende. Bild vom Autor.

Leerzeichen vor Zahlen entfernen

TRIM() eignet sich zwar hervorragend zum Bereinigen von Leerzeichen in Text, aber bei numerischen Daten kann es Probleme geben. Ich will dir zeigen, was passiert:

=TRIM(A2)

Die Funktion TRIM() entfernt die Leerzeichen nicht richtig. Bild vom Autor.

Wie du siehst, hat TRIM() die Leerzeichen vor einer Zahl entfernt, aber Excel behandelt das Ergebnis nicht mehr als Zahl, sondern als Text. Hier erfährst du, wie du das erkennst:

• Die Daten sind links- statt rechtsbündig.

• Sieh dir deine Statusleiste an - sie zeigt dir nur an, wie viele Zellen du ausgewählt hast(COUNT), nicht deren Gesamtzahl. 

• Wenn du versuchst, diese Zellen mit SUM() zu addieren, kommst du auf Null.

Um das zu beheben, musst du den Text wieder in eine Zahl umwandeln. Dazu kannst du entweder mit 1 multiplizieren oder diese Formel verwenden:

=VALUE(TRIM(A2))

Dadurch werden Leerzeichen entfernt und sichergestellt, dass Excel die Daten als Zahlen behandelt.

Kombiniere VALUE() und TRIM(), um Leerzeichen vor Zahlen zu entfernen. Bild vom Autor.

Leerzeichen aus einer Zeichenkette entfernen 

Wenn du alle Leerzeichen, auch die zwischen den Wörtern, entfernen möchtest, verwende die Funktion SUBSTITUTE():

=SUBSTITUTE(A2, " ", "")

Du kannst sehen, dass die Formel jedes Leerzeichen von der Zeichenkette abschneidet.

Entferne die Leerzeichen mit der Funktion SUBSTITUTE(). Bild vom Autor.

Sonderzeichen und unnötige Leerzeichen entfernen  

SUBSTITUTE() ist ideal, wenn du bestimmte Zeichen aus deinem Text entfernen möchtest. Aber du kannst es mit TRIM() kombinieren, um Sonderzeichen und Leerzeichen in einem Rutsch zu bereinigen. Wenn ich zum Beispiel "&" entfernen und Leerstellen aufräumen will, verwende ich diese Formel:

TRIM(SUBSTITUTE(A2, “&“, “”))

Du kannst sehen, wie TRIM() und SUBSTITUTE() zusammen funktionieren, wenn sie zusammen verwendet werden, im Gegensatz zu SUBSTITUTE() allein.

Kombiniere TRIM() und SUBSTITUTE(), um Leerzeichen zu entfernen. Bild vom Autor.

Häufige Probleme und Lösungen

Wenn ich Daten aus anderen Quellen kopiere oder importiere, tauchen manchmal eine Menge Leerzeichen und seltsame Zeichen in meinen Daten auf. So gehe ich mit ihnen um: 

Nicht umgebrochene Leerzeichen, die TRIM() nicht verarbeiten kann

Die Funktion TRIM() entfernt überflüssige Leerzeichen, aber sie funktioniert nicht bei nicht umgebenden Leerzeichen (Zeichencode 160). Diese tauchen oft in Daten von Webseiten oder formatierten Quellen auf. Um dies zu beheben, verwende ich:

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

Diese Formel ersetzt nicht-gebrochene Leerzeichen durch Standard-Leerzeichen, die TRIM() entfernen kann.

Probleme mit versteckten Zeichen oder eingefügten Daten

Manchmal schleichen sich andere versteckte Zeichen ein, wie Tabulatoren oder Zeilenumbrüche. Um sie zu finden, benutze ich CODE(), um die Position der Figur zu überprüfen:

• Erste Figur: =CODE(LEFT(A1,1))

• Letztes Zeichen: =CODE(RIGHT(A1,1))

• Besondere Position: =CODE(MID(A1, n, 1)) (Ersetze n durch die Positionsnummer.)

Das ist die Formel, die ich verwende:

(RIGHT(C2))

Sobald ich den Code kenne, entferne ich ihn mit TRIM() und SUBSTITUTE(). Um zum Beispiel Tabs (Code 115) zu entfernen, verwende ich:

(SUBSTITUTE(C2, CHAR(115), “ “))

Verwende CODE(), TRIM() und SUBSTITUTE(), um Leerzeichen zu entfernen. Bild vom Autor.

Schlussgedanken

In diesem Leitfaden habe ich dir Methoden wie TRIM() für einfache Korrekturen, SUBSTITUTE() für fortgeschrittene Probleme und Add-ins für den Fall gezeigt, dass du häufig Leerzeichen entfernst und einen anderen Prozess möchtest. Jede Methode eignet sich für unterschiedliche Datentypen, also wähle diejenige, die deinen Bedürfnissen entspricht.

Wenn du deine Fähigkeiten weiter ausbauen möchtest, schau dir den Lernpfad " Datenanalyse mit Excel Power Tools " im DataCamp an. Es bietet Schritt-für-Schritt-Lektionen, die dir helfen, Excel zu beherrschen und deine Datenbereinigungstechniken zu verbessern.