Excel WECHSELN, GLÄTTEN und GROSS2: Daten bereinigen
Importierte Listen sind selten sauber: doppelte Leerzeichen, Namen in Großbuchstaben, Telefonnummern mit Bindestrichen, Preise, die Excel nur als Text sieht. Mit vier Funktionen räumst du solche Daten in einem Rutsch auf.
Kurz gesagt: =GLÄTTEN(A2) entfernt überflüssige Leerzeichen, =GROSS2(A2) schreibt jedes Wort mit großem Anfangsbuchstaben, =WECHSELN(A2;"-";"") löscht alle Bindestriche und =WERT(A2) macht aus einer Text-Zahl eine echte Zahl.
Probier es gleich aus
Die Namen kommen aus einem Online-Formular. Jeder hat sie anders eingetippt. Mach daraus eine saubere Liste, mit der du sortieren, suchen und Serienbriefe schreiben kannst.
Aufgabe Die Namen kommen aus einem Online-Formular: mal klein, mal groß geschrieben, mit überflüssigen Leerzeichen. Schreib in B2 eine Formel, die den Namen sauber liefert, also ohne zusätzliche Leerzeichen und mit großen Anfangsbuchstaben (Jana Weber). Die Formel wird bis B7 ausgefüllt.
| A | B | C | |
|---|---|---|---|
| 1 | Name (Import) | Name bereinigt | Abteilung |
| 2 | jana weber | Vertrieb | |
| 3 | MURAT KAYA | Einkauf | |
| 4 | lena hoffmann | Controlling | |
| 5 | Tom Schulz | Vertrieb | |
| 6 | sara KLEIN | Personal | |
| 7 | ali demir | Logistik |
Die Funktionen zum Bereinigen im Überblick
| Funktion | Was sie tut | Beispiel |
|---|---|---|
| GLÄTTEN | Entfernt Leerzeichen am Anfang und Ende, lässt zwischen Wörtern nur eines stehen | =GLÄTTEN(" Anna Weber ") ergibt „Anna Weber“ |
| GROSS2 | Erster Buchstabe jedes Wortes groß, der Rest klein | =GROSS2("ANNA WEBER") ergibt „Anna Weber“ |
| GROSS, KLEIN | Alles groß oder alles klein | =KLEIN("Info@Firma.de") ergibt „info@firma.de“ |
| WECHSELN | Ersetzt einen bestimmten Text durch einen anderen | =WECHSELN("030-1234";"-";"") ergibt „0301234“ |
| WERT | Wandelt eine Zahl, die als Text gespeichert ist, in eine Zahl um | =WERT("12,50") ergibt 12,5 |
| SÄUBERN | Entfernt nicht druckbare Steuerzeichen, etwa Zeilenumbrüche aus Exporten | =SÄUBERN(A2) |
Die Funktionen lassen sich beliebig verschachteln. =GROSS2(GLÄTTEN(A2)) erledigt Leerzeichen und Schreibweise in einem Schritt. Die innere Funktion rechnet zuerst und gibt ihr Ergebnis an die äußere weiter.
Zeichen ersetzen und entfernen mit WECHSELN
WECHSELN sucht in einem Text nach einer Zeichenfolge und tauscht sie gegen eine andere. Zum Entfernen ersetzt du durch einen leeren Text, also zwei Anführungszeichen ohne Inhalt.
| Argument | Bedeutung | Im Beispiel |
|---|---|---|
| Text | Die Zelle mit dem Ausgangstext | B2 |
| Alter_Text | Was ersetzt werden soll | "-" |
| Neuer_Text | Was stattdessen stehen soll | "" (nichts) |
| ntes_Auftreten | Optional: nur das soundsovielte Vorkommen ersetzen | weggelassen, also alle |
Aufgabe Für den Import in die Telefonanlage dürfen die Nummern keine Bindestriche enthalten. Schreib in C2 eine Formel, die alle Bindestriche aus der Nummer in B2 entfernt. Die Formel wird bis C7 ausgefüllt.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Telefon | Telefon bereinigt |
| 2 | Jana Weber | 0151-2384-907 | |
| 3 | Murat Kaya | 030-5571-22 | |
| 4 | Lena Hoffmann | 0172-9930-415 | |
| 5 | Tom Schulz | 040-338-1290 | |
| 6 | Sara Klein | 0160-4402-873 | |
| 7 | Ali Demir | 089-2140-66 |
Mehrere verschiedene Zeichen entfernst du, indem du WECHSELN verschachtelst: =WECHSELN(WECHSELN(B2;"-";"");" ";"") löscht erst die Bindestriche, dann die Leerzeichen. Für jedes weitere Zeichen kommt eine Ebene dazu.
Musst du eine Liste nur einmal bereinigen, geht es auch ohne Formel: Strg+H öffnet „Suchen und Ersetzen“. Das ändert die Zellen direkt. Die Formel hat den Vorteil, dass das Original erhalten bleibt und neue Zeilen mit bereinigt werden.
Text-Zahlen in echte Zahlen umwandeln
Zahlen aus Shopsystemen, Banken oder CSV-Dateien landen oft als Text in der Zelle. Du erkennst das daran, dass sie linksbündig stehen, ein grünes Dreieck in der Ecke haben und SUMME 0 liefert. Häufigster Grund ist das falsche Dezimaltrennzeichen: Ein deutsches Excel erwartet 12,50, das englische System liefert 12.50.
Aufgabe Die Preise stammen aus einem englischsprachigen Shopsystem und stehen als Text mit Dezimalpunkt in Spalte B. Damit rechnet dein deutsches Excel nicht. Schreib in C2 eine Formel, die den Punkt durch ein Komma ersetzt und daraus eine echte Zahl macht. Die Formel wird bis C7 ausgefüllt.
| A | B | C | |
|---|---|---|---|
| 1 | Artikelnr. | Preis (Export) | Preis als Zahl |
| 2 | A-118 | 12.50 | |
| 3 | A-104 | 4.95 | |
| 4 | A-131 | 129.00 | |
| 5 | A-109 | 0.89 | |
| 6 | A-125 | 18.40 | |
| 7 | A-112 | 56.75 |
Bei Beträgen mit Tausenderpunkt und Komma wie „1,234.50“ brauchst du zwei Schritte: =WERT(WECHSELN(WECHSELN(B2;",";"");".";",")) entfernt zuerst das Komma und tauscht dann den Punkt. Kürzer geht es mit ZAHLENWERT, dort gibst du die Trennzeichen direkt an: =ZAHLENWERT(B2;".";","). Das zweite Argument ist das Dezimaltrennzeichen im Text, das dritte das Tausendertrennzeichen.
Groß- und Kleinschreibung vereinheitlichen
GROSS2 ist ideal für Namen und Orte, hat aber Grenzen: Es setzt nach jedem Leerzeichen, Bindestrich oder Apostroph einen Großbuchstaben. Aus „von der heide“ wird „Von Der Heide“, aus „mcdonald“ wird „Mcdonald“. Solche Einzelfälle korrigierst du danach von Hand. Für E-Mail-Adressen und Benutzernamen nimmst du KLEIN, für Artikelcodes und Kennzeichen GROSS.
Wichtig für Vergleiche: SVERWEIS, ZÄHLENWENN und der Gleich-Vergleich unterscheiden nicht zwischen Groß- und Kleinschreibung. „BERLIN“ und „Berlin“ sind für sie gleich. WECHSELN und FINDEN dagegen schon, =WECHSELN(A2;"gmbh";"GmbH") findet „GMBH“ nicht.
Typische Fehler beim Bereinigen
GLÄTTEN entfernt manche Leerzeichen nicht
GLÄTTEN entfernt nur das normale Leerzeichen. Daten aus Webseiten enthalten oft geschützte Leerzeichen (Zeichencode 160), die genauso aussehen. Tausch sie vorher aus: =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);" ")). Das ist auch eine häufige Ursache, wenn der SVERWEIS trotz scheinbar gleicher Werte #NV liefert.
WERT liefert #WERT!
WERT kann den Text nicht als Zahl lesen. Meist steckt noch ein Zeichen drin, etwa ein Währungssymbol, eine Einheit wie „kg“ oder das falsche Trennzeichen. Entferne es vorher mit WECHSELN, dann klappt die Umwandlung.
Die bereinigten Daten verschwinden
Die bereinigte Spalte besteht aus Formeln. Löschst du die Ausgangsspalte, zeigen sie #BEZUG!. Kopier deshalb die bereinigte Spalte und füg sie per Rechtsklick unter Einfügeoptionen > Werte als feste Werte ein. Danach kannst du das Original löschen.
Führende Nullen gehen verloren
Das Ergebnis von WECHSELN ist Text, Telefonnummern und Postleitzahlen behalten ihre 0 am Anfang. Wandelst du sie mit WERT um, ist die 0 weg. Für solche Nummern also kein WERT verwenden.
WECHSELN und GLÄTTEN auf Englisch
In der englischen Excel-Version heißen die Funktionen anders, und Argumente werden mit Komma getrennt. =WERT(WECHSELN(B2;".";",")) wird dort zu =VALUE(SUBSTITUTE(B2,",",".")), denn im englischen Excel ist der Punkt das Dezimalzeichen.
| Deutsch | Englisch |
|---|---|
| WECHSELN, ERSETZEN | SUBSTITUTE, REPLACE |
| GLÄTTEN, SÄUBERN | TRIM, CLEAN |
| GROSS, KLEIN, GROSS2 | UPPER, LOWER, PROPER |
| WERT, ZAHLENWERT | VALUE, NUMBERVALUE |
| ZEICHEN | CHAR |
In welchen Excel-Versionen gibt es diese Funktionen?
WECHSELN, ERSETZEN, GLÄTTEN, SÄUBERN, GROSS, KLEIN, GROSS2 und WERT gibt es in allen Excel-Versionen, auch in Excel für Mac und Excel für das Web. ZAHLENWERT kam mit Excel 2013 dazu und ist damit in allen aktuellen Versionen vorhanden.
Häufige Fragen
Wie entferne ich alle Leerzeichen aus einer Zelle?
GLÄTTEN lässt zwischen Wörtern ein Leerzeichen stehen. Sollen wirklich alle weg, etwa bei einer IBAN, nimm =WECHSELN(A2;" ";"").
Wie ersetze ich in Excel Punkt durch Komma?
Mit =WECHSELN(A2;".";","). Soll das Ergebnis eine Zahl sein, pack WERT darum: =WERT(WECHSELN(A2;".";",")). Einmalig geht es auch mit Strg+H.
Was ist der Unterschied zwischen WECHSELN und ERSETZEN?
WECHSELN sucht nach einem bestimmten Text, egal wo er steht. ERSETZEN arbeitet mit der Position: =ERSETZEN(A2;1;3;"NEU") ersetzt die ersten drei Zeichen. Für Bereinigungen ist WECHSELN fast immer die richtige Wahl.
Wie entferne ich Zeilenumbrüche in einer Zelle?
Ein Zeilenumbruch ist das Zeichen 10. =WECHSELN(A2;ZEICHEN(10);" ") ersetzt ihn durch ein Leerzeichen. SÄUBERN entfernt ihn ebenfalls, setzt aber kein Leerzeichen ein.
Mehr zum Zerlegen und Verbinden von Texten findest du bei den Textfunktionen.