Excel-Anleitungen

Daten bereinigen in Excel: unordentliche Exporte mit Formeln aufräumen

Räum unordentliche Exporte mit Formeln auf. Entferne Leerzeichen, korrigier Großschreibung, teil Namen und Codes auf, find Duplikate und mach aus Text Zahlen.

Von Nicolaj, Gründer von ExcelPraxis · · 12 Min. Lesezeit

Fast keine Daten landen gebrauchsfertig in Excel. Eine Kundenliste aus dem CRM hat Namen in Großbuchstaben, Codes mit einem Leerzeichen am Ende und Telefonnummern in fünf verschiedenen Formaten. Ein Bericht aus der Buchhaltungssoftware hat Zahlen, die SUMME nicht addieren will. Ein Anmeldeformular hat dieselbe Person dreimal. Bevor du addieren, nachschlagen oder eine Übersicht bauen kannst, musst du aufräumen.

Du kannst von Hand aufräumen, eine Zelle nach der anderen, und bei zehn Zeilen ist das in Ordnung. Bei tausend Zeilen oder einem Export, den du nächsten Monat wieder bekommst, sind Formeln schneller und zuverlässiger, und du kannst sie wiederverwenden. Hier bekommst du die Aufräumarbeiten, die am häufigsten vorkommen, die Formeln, die sie lösen, und eine Reihenfolge, der du folgen kannst, damit du keine neuen Probleme schaffst, während du die alten löst.

Bevor du anfängst: behalt das Original

Erste Regel: Überschreib nie die ursprünglichen Daten. Lass den Rohexport in Ruhe auf einem eigenen Blatt oder in den ursprünglichen Spalten und räum in Hilfsspalten daneben auf. Eine Hilfsspalte ist eine Spalte mit Formeln, die den unordentlichen Wert liest und dir den sauberen liefert.

Hilfsspalten haben drei Vorteile gegenüber dem direkten Ändern in den Zellen:

  • Du siehst, was sich geändert hat. Der rohe und der saubere Wert stehen nebeneinander, also kannst du jede Zeile prüfen.
  • Nichts geht verloren. Ist eine Formel falsch, korrigierst du sie und kopierst sie wieder nach unten. Das Original ist noch da.
  • Es funktioniert wieder. Nächsten Monat fügst du den neuen Export in die Rohspalten ein, und die Hilfsspalten räumen genau gleich auf.

Wenn du zufrieden bist, kannst du die Formeln mit Inhalte einfügen und Werte in feste Werte umwandeln. Danach kannst du die Rohspalten löschen, wenn du sie nicht mehr brauchst. Mach das am Ende und nicht am Anfang.

Find zuerst die Probleme

Du kannst nichts korrigieren, was du nicht gesehen hast, und unordentliche Daten sehen oft ordentlich aus. Ein paar schnelle Prüfungen zeigen dir, womit du es zu tun hast:

  • Öffne die Filterliste. Schalt die Filter mit Strg+Umschalt+L ein und öffne die Dropdown-Liste einer Spalte mit Kategorien. Jede Schreibweise steht für sich, also tauchen „Frankfurt“, „Frankfurt “ und „frankfurt“ als drei verschiedene auf.
  • Zähl die Zeichen. LÄNGE (LEN) liefert die Anzahl der Zeichen in einer Zelle, und Leerzeichen zählen mit. Ist ein Wert länger, als er aussieht, versteckt er Leerzeichen:
B2=LÄNGE(A2)
AB
1Nachname
2Weber 6

LÄNGE zählt 6 Zeichen in einem Nachnamen mit 5 Buchstaben. Das sechste ist ein Leerzeichen am Ende, das du in der Zelle nicht siehst.

  • Schau auf die Ausrichtung in der Zelle. Wenn niemand das geändert hat, stehen Zahlen und Datumswerte rechts und Text links. Stehen ein paar Beträge in einer Spalte links, sind es Zahlen, die als Text gespeichert sind.
  • Vergleich ANZAHL mit ANZAHL2. In einer Spalte, die nur Zahlen haben sollte, zählt =ANZAHL(B2:B500) die Zahlen, während =ANZAHL2(B2:B500) alles zählt, was nicht leer ist. Sind die beiden verschieden, sind einige der Werte Text.
  • Zähl die leeren. =ANZAHLLEEREZELLEN(B2:B500) sagt dir, wie viele Werte fehlen. Die Übung mit ANZAHLLEEREZELLEN trainiert das.

Fünf Minuten Prüfen sagen dir, welche der Abschnitte unten du brauchst.

Überflüssige Leerzeichen: GLÄTTEN

Überflüssige Leerzeichen sind das häufigste Problem in exportierten Daten. Sie sind auch der häufigste Grund, warum ein Verweis #NV für einen Wert liefert, den du mit eigenen Augen sehen kannst. „A-104 “ mit einem Leerzeichen am Ende ist nicht dasselbe wie „A-104“.

GLÄTTEN (TRIM) entfernt alle Leerzeichen am Anfang und am Ende eines Texts und macht aus mehreren Leerzeichen zwischen Wörtern eines:

B2=GLÄTTEN(A2)
AB
1Rohname
2 Lena Weber Lena Weber

GLÄTTEN entfernt die Leerzeichen vor und nach dem Text und lässt ein Leerzeichen zwischen den Wörtern stehen.

Üb das in Überflüssige Leerzeichen entfernen. Du kannst die Funktion auch direkt in eine andere Formel setzen, statt eine Hilfsspalte zu nehmen. Zum Beispiel =SVERWEIS(GLÄTTEN(E1);A2:C50;3;FALSCH), wenn nur der Suchwert unordentlich ist.

Zwei Arten von „Leerzeichen“ schafft die Funktion nicht:

  • Geschützte Leerzeichen von Webseiten und aus manchen Systemen. Sie sehen aus wie ein ganz normales Leerzeichen, sind aber ein anderes Zeichen, Nummer 160. Mach zuerst normale Leerzeichen daraus und entferne dann die überflüssigen: =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);" ")).
  • Zeilenumbrüche und andere unsichtbare Zeichen aus Text, den du aus einer Mail oder einem mehrzeiligen Formularfeld kopiert hast. SÄUBERN entfernt sie: =GLÄTTEN(SÄUBERN(A2)).

Scheint die Funktion nicht zu wirken, nimm LÄNGE vorher und nachher. Dann siehst du, ob eins dieser beiden Zeichen dahintersteckt.

Groß- und Kleinschreibung: GROSS2, GROSS und KLEIN

Exporte haben oft Namen in Großbuchstaben oder die zufällige Mischung, die die Leute eben eingetippt haben. Drei Funktionen korrigieren das:

  • GROSS2 (PROPER) gibt jedem Wort einen großen Anfangsbuchstaben und schreibt den Rest klein: lena weber wird zu Lena Weber.
  • GROSS (UPPER) schreibt alles groß. Das passt zu Codes und IDs.
  • KLEIN (LOWER) schreibt alles klein. Das passt zu E-Mail-Adressen.

Sie passen gut zu GLÄTTEN. Setz GLÄTTEN nach innen, damit die Leerzeichen weg sind, bevor die Buchstaben korrigiert werden:

B2=GROSS2(GLÄTTEN(A2))
AB
1Rohname
2lENA wEBERLena Weber

GLÄTTEN läuft zuerst und entfernt das doppelte Leerzeichen. Danach gibt GROSS2 jedem Wort einen großen Anfangsbuchstaben.

GROSS2 ist für Namen nicht perfekt. Aus anna-lena macht es Anna-Lena, und das stimmt. Aber aus LUDWIG VAN BEETHOVEN macht es Ludwig Van Beethoven und aus MCDONALD'S Mcdonald'S, weil es nach jedem Bindestrich, Apostroph und Leerzeichen großschreibt. Für eine Kundenliste reicht das meistens. Soll der Name auf ein Zertifikat oder einen Umschlag gedruckt werden, prüf die Ausnahmen von Hand. Üb die Funktionen in Namen mit GROSS2 korrigieren und Einen Code mit GROSS bereinigen.

Codes haben oft einen festen Aufbau. Die ersten drei Zeichen sind eine Abteilung, die letzten vier ein Jahr, und die Artikelnummer steht in der Mitte. Wenn die Positionen immer gleich sind, können drei Funktionen den Text zerschneiden:

  • LINKS (LEFT) nimmt Zeichen vom Anfang: =LINKS(A2;3).
  • RECHTS (RIGHT) nimmt Zeichen vom Ende: =RECHTS(A2;4).
  • TEIL (MID) nimmt Zeichen aus der Mitte ab einer Startposition: =TEIL(A2;5;4) nimmt vier Zeichen ab dem fünften.
B2=LINKS(A2;3)
AB
1Bestellcode
2BER-20441BER

In einfachen Worten: Nimm die ersten 3 Zeichen vom Bestellcode in A2.

Die ersten drei Zeichen jedes Bestellcodes sind die Abteilung, also holt LINKS mit 3 sie heraus.

Die drei Übungen mit LINKS, RECHTS und TEIL fangen mit dem Präfix an und gehen weiter mit den letzten Ziffern und einem Jahr mitten im Text.

Eins solltest du wissen: Alle drei liefern Text, auch wenn die Zeichen Ziffern sind. =RECHTS(A2;4) auf BER-2044 liefert den Text "2044" und nicht die Zahl. Musst du damit rechnen, pack es in WERT: =WERT(RECHTS(A2;4)).

Text an einem Zeichen aufteilen: FINDEN und SUCHEN

Namen, E-Mail-Adressen und viele Codes haben keine festen Positionen. Der Vorname ist alles vor dem ersten Leerzeichen, egal wo es steht. Die Domain ist alles nach dem @. Hier findest du zuerst heraus, wo das Zeichen steht, und schneidest dann daran.

FINDEN liefert dir die Position eines Texts in einem anderen: =FINDEN("@";A2) ist 11 für lena.weber@example.com, weil das @ das 11. Zeichen ist. Kombinier es mit LINKS, dann bekommst du alles vor dem Zeichen:

B2=LINKS(A2;FINDEN(" ";A2)-1)
AB
1Vollständiger Name
2Lena WeberLena

In einfachen Worten: Nimm alles vor dem Leerzeichen vom Anfang des vollständigen Namens in A2.

FINDEN liefert 5, die Position des Leerzeichens. LINKS nimmt dann die 4 Zeichen davor.

Die -1 ist wichtig. Ohne sie nimmt LINKS das Leerzeichen mit. Willst du alles nach einem Zeichen, nimm FINDEN zusammen mit TEIL und fang ein Zeichen später an:

B2=TEIL(A2;FINDEN("@";A2)+1;100)
AB
1E-Mail
2lena.weber@example.comexample.com

In einfachen Worten: Nimm bis zu 100 Zeichen der E-Mail-Adresse in A2 ab dem Zeichen direkt nach dem @.

TEIL beginnt ein Zeichen nach dem @ und nimmt bis zu 100 Zeichen. Das ist der Rest des Texts.

Die 100 bedeutet nur „mehr Zeichen, als es geben kann“. TEIL hört auf, wenn der Text endet, also funktioniert jede ausreichend große Zahl. Das sind einige der meistgenutzten Textformeln im Büro, und die Übungen Das @ in einer E-Mail-Adresse finden, Der Name vor dem @ und Die Domain nach dem @ führen dich in dieser Reihenfolge durch.

SUCHEN macht dasselbe wie FINDEN, aber Groß- und Kleinschreibung ist ihm egal, und es versteht Platzhalter. Nimm FINDEN, wenn das Zeichen ganz genau ist, wie ein Leerzeichen, ein Bindestrich oder ein @. Nimm SUCHEN, wenn du nach einem Wort suchst, das groß oder klein geschrieben sein kann.

TEXTVOR und TEXTNACH

Microsoft 365 hat zwei Funktionen, die in einem Schritt finden und schneiden: =TEXTVOR(A2;" ") liefert den Text vor dem ersten Leerzeichen, und =TEXTNACH(A2;"@") liefert den Text nach dem @. Sie sind leichter zu lesen und leichter richtig hinzubekommen. Der Nachteil ist derselbe wie bei allen neuen Funktionen: Ältere Versionen zeigen #NAME?. Üb sie in Den Vornamen mit TEXTVOR holen und „Nachname, Vorname“ mit TEXTNACH umdrehen.

Aufteilen ohne Formeln

Musst du nur einmal aufräumen, können zwei eingebaute Werkzeuge schneller sein als Formeln:

  • Text in Spalten (Registerkarte Daten) teilt eine Spalte an einem Zeichen, etwa einem Komma oder einem Leerzeichen, auf einmal in mehrere Spalten auf.
  • Blitzvorschau (Strg+E) schaut zu, wie du das Ergebnis in die erste Zeile oder zwei schreibst, und füllt den Rest aus, indem es das Muster nachmacht.

Beide ändern deine Daten einmal. Sie aktualisieren sich nicht, wenn sich die Quelle ändert, und die Blitzvorschau kann bei Zeilen, die anders aussehen, falsch raten, also prüf das Ergebnis. Sind es Daten, die du jeden Monat aufräumst, sind Formeln die bessere Investition.

Zeichen ersetzen oder entfernen: WECHSELN

WECHSELN (SUBSTITUTE) ersetzt jedes Vorkommen eines Texts durch einen anderen. Ersetzt du ihn durch nichts, "", entfernt es die Zeichen:

B2=WECHSELN(A2;" ";"")
AB
1Telefon
20221 4455 660221445566

In einfachen Worten: Ersetze jedes Leerzeichen in der Telefonnummer in A2 durch nichts.

WECHSELN ersetzt jedes Leerzeichen durch nichts, sodass nur die Ziffern übrig bleiben.

Üb das in Die Bindestriche mit WECHSELN entfernen. Musst du mehrere verschiedene Zeichen entfernen, setz ein WECHSELN in ein anderes: =WECHSELN(WECHSELN(A2;"(";"");")";"") entfernt beide Arten von Klammern.

WECHSELN unterscheidet zwischen Groß- und Kleinschreibung, und es ersetzt alle Vorkommen, es sei denn, du wählst mit dem vierten Argument ein bestimmtes aus. Suchen und Ersetzen macht dasselbe direkt in den Zellen ohne Formel, also nimm es an einer Kopie.

Text zusammensetzen: & und TEXTVERKETTEN

Beim Aufräumen geht es genauso oft ums Zusammensetzen wie ums Aufteilen. Ein vollständiger Name aus Vor- und Nachname. Eine Adresse in einer Zeile. Ein Schlüssel aus zwei Spalten für einen Verweis.

Das Zeichen & verbindet Texte: =A2&" "&B2 setzt ein Leerzeichen zwischen Vor- und Nachname. Üb das in Vor- und Nachname mit & verbinden. Für zwei oder drei Teile ist das gut. Hast du mehr oder können einige leer sein, ist TEXTVERKETTEN (TEXTJOIN) besser. Es nimmt ein Trennzeichen, WAHR oder FALSCH dafür, ob leere Zellen übersprungen werden, und dann die Zellen, die verbunden werden sollen:

E2=TEXTVERKETTEN(", ";WAHR;A2:C2)
ABCDE
1StraßeZusatzOrt
2Hauptstraße 1250667 KölnHauptstraße 12, 50667 Köln

In einfachen Worten: Verbinde die Teile der Adresse in A2:C2 mit einem Komma und einem Leerzeichen dazwischen und überspring die leeren.

TEXTVERKETTEN setzt Komma und Leerzeichen zwischen die Teile, und WAHR überspringt die leere Zelle mit dem Adresszusatz.

Mit & hätte der leere Zusatz ein doppeltes Komma ergeben: „Hauptstraße 12, , 50667 Köln“. Die Übung mit der Adresse trainiert TEXTVERKETTEN mit einem leeren Teil.

Nimm nicht Verbinden und zentrieren, um zwei Zellen zusammenzusetzen. Es behält nur den ersten Wert und wirft den Rest weg. Die Anleitung zum Verbinden von Zellen erklärt, was Verbinden macht, wie du es in einem unordentlichen Export aufhebst und wann eine Formel die bessere Wahl ist.

Zahlen, die als Text gespeichert sind

Das ist das teuerste Datenproblem, denn es liefert falsche Summen ohne jeden Fehler. Systeme exportieren Zahlen oft als Text, und dasselbe passiert, wenn du von einer Webseite oder aus einem PDF kopierst. Die Zelle zeigt 350, aber Excel sieht ein Wort:

B5=SUMME(B2:B4)
AB
1ArtikelBetrag
2Papier1200
3Toner350
4Etiketten800
52000

Die 350 in B3 sind Text und keine Zahl, also überspringt SUMME sie. Die Summe ist 2.000 statt 2.350, und du bekommst keinen Fehler.

Anzeichen dafür, dass eine Spalte Zahlen als Text enthält: Die Werte stehen links in der Zelle, es gibt ein kleines grünes Dreieck in der Ecke, ANZAHL ist kleiner als ANZAHL2, und SUMME, SUMMEWENN und Verweise liefern Antworten, die ein kleines bisschen falsch sind.

So korrigierst du sie:

  • Das Warnmenü. Markier die Zellen mit dem grünen Dreieck, klick auf das Warnsymbol und wähl In eine Zahl umwandeln. Schnell, wenn es nur eine Spalte ist.
  • Text in Spalten. Markier die Spalte, wähl Daten > Text in Spalten und klick auf Fertig stellen, ohne etwas zu ändern. Excel liest jeden Wert neu und macht aus denen, die wie Zahlen aussehen, Zahlen.
  • WERT in einer Hilfsspalte. =WERT(B3) liefert die Zahl. Es funktioniert auch mit Text, der zuerst bereinigt werden muss, zum Beispiel =WERT(GLÄTTEN(B3)).
  • Eine Berechnung. Multiplizierst du mit 1 oder addierst 0, wird eine Textzahl ebenfalls umgewandelt, also funktioniert =B3*1. WERT sagt nur deutlicher, was du meinst.

Achte auf die Dezimaltrennzeichen. Text wie 1,250.50 aus einem englischen oder amerikanischen System ist für deutsches Excel keine Zahl, und WERT liefert #WERT!. Tausch die Trennzeichen zuerst mit WECHSELN oder importier die Datei mit dem richtigen Gebietsschema.

Datumswerte haben dasselbe Problem und dieselben Anzeichen. In unserer Anleitung zu Datumswerten in Excel siehst du, wie du ein Textdatum mit DATUM, LINKS, TEIL und RECHTS neu aufbaust.

Duplikate

Derselbe Kunde zweimal. Dieselbe Rechnung zweimal importiert. Dieselbe E-Mail-Adresse über drei Formulare angemeldet. Duplikate blähen Anzahlen und Summen auf, und ein Verweis auf einen Wert, der mehrmals vorkommt, hält beim ersten Treffer an, ohne dich zu warnen.

Der sicherste Weg, sie zu finden, ist eine Hilfsspalte mit ZÄHLENWENN (COUNTIF). Sie zählt, wie oft jeder Wert in der ganzen Spalte vorkommt:

B4=ZÄHLENWENN($A$2:$A$6;A4)
AB
1E-Mail
2anna@example.com
3ben@example.com
4anna@example.com2
5clara@example.com
6david@example.com

In einfachen Worten: Zähl, wie oft die E-Mail-Adresse in A4 in der ganzen Liste in A2:A6 vorkommt.

anna@example.com steht zweimal da, also ist die Zahl 2. Kopier die Formel durch die ganze Spalte, dann ist alles über 1 ein Duplikat.

Die $-Zeichen halten den Bereich fest, wenn du nach unten kopierst, während A4 mit jeder Zeile mitwandert. Filter die Hilfsspalte, sodass du nur Werte über 1 siehst. Dann hast du alle Duplikate mit dem Rest der Zeile daneben und kannst entscheiden, welches du behältst.

Willst du die Duplikate direkt in den Daten sehen statt in einer Hilfsspalte, färb die Zeilen mit einer Regel der bedingten Formatierung. Die Anleitung zur bedingten Formatierung zeigt die Regel mit ZÄHLENWENN, die das macht.

Zwei weitere Werkzeuge:

  • Daten > Duplikate entfernen löscht doppelte Zeilen an Ort und Stelle und behält die erste. Das ist schnell und lässt sich nicht rückgängig machen, also nimm es an einer Kopie und prüf die Anzahl, die Excel zurückmeldet.
  • EINDEUTIG (UNIQUE) in Excel 365 liefert dir die Liste ohne Duplikate als neuen Bereich: =EINDEUTIG(A2:A500). Es ist perfekt, wenn du eine saubere Liste mit Kategorien oder Kunden für eine Übersicht brauchst.

Räum die Spalte auf, bevor du nach Duplikaten suchst. Für ZÄHLENWENN sind „anna@example.com“ und „Anna@example.com “ keine Duplikate, bis GLÄTTEN und KLEIN sie gleich gemacht haben.

Kategorien, die nicht übereinstimmen

Eine Spalte mit „Ffm.“, „Frankfurt am Main“ und „Frankfurt“ sind für SUMMEWENN und eine Pivot-Tabelle drei verschiedene Städte. GLÄTTEN und GROSS2 korrigieren Leerzeichen und Buchstaben, aber sie können nicht wissen, dass Ffm. Frankfurt am Main bedeutet.

Dafür baust du eine kleine Übersetzungstabelle: alle Varianten in einer Spalte und den sauberen Namen in der nächsten. Ein Verweis tauscht dann jede Variante gegen den sauberen Namen: =XVERWEIS(GLÄTTEN(B2);$H$2:$H$20;$I$2:$I$20;B2). Das letzte Argument liefert den ursprünglichen Wert zurück, wenn er nicht in der Tabelle steht, sodass neue Varianten auftauchen, statt zu verschwinden. Jedes Mal, wenn die Filterliste eine neue Schreibweise zeigt, trägst du sie in die Tabelle ein. In unserer Anleitung zu Verweisen liest du mehr über XVERWEIS und die Alternativen.

Ist die Spalte sauber, halt sie sauber. Mit einer Dropdown-Liste mit den sauberen Namen kann von Anfang an niemand eine neue Variante eintippen.

Eine Reihenfolge, die funktioniert

Die Reihenfolge ist wichtig, weil manche Schritte von anderen abhängen. Du kannst keine Duplikate finden, bevor die Leerzeichen weg sind, und du kannst keine Zahlen umwandeln, solange sie noch Währungszeichen haben. Diese Reihenfolge funktioniert für die meisten Exporte:

  1. Kopier die Rohdaten auf ein eigenes Blatt und lass sie in Ruhe.
  2. Entferne unsichtbare Zeichen und überflüssige Leerzeichen mit SÄUBERN, WECHSELN für ZEICHEN(160) und GLÄTTEN.
  3. Korrigier die Schreibweise mit GROSS2, GROSS oder KLEIN.
  4. Teil Spalten auf, die mehr als eine Sache enthalten, etwa vollständige Namen, Codes mit mehreren Teilen oder Postleitzahl und Ort in derselben Zelle.
  5. Wandle Zahlen und Datumswerte, die als Text gespeichert sind, in echte Zahlen und Datumswerte um.
  6. Vereinheitliche Kategorien auf eine Schreibweise mit einer Übersetzungstabelle.
  7. Find die Duplikate und entscheide über sie, jetzt, wo gleiche Werte tatsächlich gleich sind.
  8. Prüf das Ergebnis: ANZAHL gegen ANZAHL2, eine Summe gegen das Quellsystem und ein paar Zeilen von Hand.
  9. Füg als Werte ein, wenn die bereinigten Daten woanders weiterverwendet werden, und speicher die Arbeitsmappe mit den Formeln fürs nächste Mal.

Datenbereinigung in Excel-Tests

Datenbereinigung ist fester Bestandteil von Tests für Stellen in Analyse, Verwaltung und Betrieb, weil genau das in diesen Jobs jeden Tag gemacht wird. Typische Aufgaben:

  • „Entferne die überflüssigen Leerzeichen aus den Namen in Spalte A.“ GLÄTTEN in einer Hilfsspalte, nach unten kopiert.
  • „Zeig alle Namen mit großem Anfangsbuchstaben.“ GROSS2, oft mit GLÄTTEN innen.
  • „Hol den Vornamen“ oder „die Domain aus jeder E-Mail-Adresse“. LINKS oder TEIL mit FINDEN, oder TEXTVOR und TEXTNACH, wenn das Excel im Test sie hat.
  • „Bilde eine ID aus Regionscode und Bestellnummer.“ & oder TEXTVERKETTEN.
  • „Warum ist diese Summe falsch?“ Zahlen, die als Text gespeichert sind, oder eine doppelte Zeile. Prüf mit ANZAHL, ANZAHL2 und ZÄHLENWENN.

Tests prüfen meistens jede einzelne Zeile, und die unordentlichen Zeilen sind mit Absicht eingebaut: ein Name ohne Leerzeichen, eine E-Mail-Adresse in Großbuchstaben oder ein Code, der ein Zeichen kürzer ist als die anderen. Bevor du weitermachst, schau dir die Extremfälle in deinem Ergebnis an und nicht nur die erste Zeile. Die Testseiten unten haben Bereinigungsfragen im Format, das der Test zur Datenanalyse nutzt.

Kurz gesagt

Räum in Hilfsspalten neben den ursprünglichen Daten auf, damit du jede Änderung siehst und sie nächsten Monat wiederholen kannst. Find die Probleme zuerst mit Filtern, LÄNGE, ANZAHL und ANZAHL2. GLÄTTEN entfernt Leerzeichen. GROSS2, GROSS und KLEIN korrigieren die Schreibweise. LINKS, RECHTS und TEIL schneiden an festen Positionen, und FINDEN oder SUCHEN sagen dir, wo du schneiden musst, wenn die Positionen wechseln. WECHSELN entfernt Zeichen, & und TEXTVERKETTEN setzen Text zusammen, und WERT macht aus Text Zahlen. Find Duplikate mit ZÄHLENWENN, wenn die Werte sauber sind, und füg erst ganz am Ende als Werte ein.

Willst du alles an einer größeren Datei ausprobieren, lad die Datei mit unordentlichen Daten herunter: 60 exportierte Bestellungen mit allen Problemen aus dieser Anleitung, 14 Aufgaben und die Lösungen.

Die Übungen unten nehmen sie sich einzeln vor, an kleinen Tabellen, die absichtlich unordentlich gemacht wurden, in derselben Reihenfolge wie hier. Fang mit der ersten an und schreib die Formel selbst.

Nicolaj

Über den Autor

Nicolaj, Gründer von ExcelPraxis

Ich bin Softwareentwickler und habe ExcelPraxis von Grund auf gebaut, auch die Tabelle selbst und die Engine, die die Formeln berechnet. Damit das funktioniert, musste ich jede Funktion bis ins Detail verstehen: wie sich SVERWEIS verhält, wenn ein Code ein Leerzeichen zu viel hat, warum ein Datum eigentlich eine Zahl ist und wann RUNDEN ein anderes Ergebnis liefert als das Zahlenformat.

Mehr über Nicolaj

Üb, was diese Anleitung behandelt

Übungen mit Korrektur in einer echten Tabelle, in der Reihenfolge der Anleitung.

Funktionsseiten

Tests auf Zeit

Fragen

Wie entferne ich überflüssige Leerzeichen in Excel?

Schreib =GLÄTTEN(A2) in eine Hilfsspalte und kopier sie nach unten. Die Funktion entfernt Leerzeichen am Anfang und am Ende und macht aus mehreren Leerzeichen zwischen Wörtern eines. Leerzeichen von Webseiten sind oft geschützte Leerzeichen, die die Funktion stehen lässt. Die entfernst du mit =GLÄTTEN(WECHSELN(A2;ZEICHEN(160);" ")).

Wie teile ich einen vollständigen Namen in Vor- und Nachname auf?

Steht der Name als „Vorname Nachname“ da, liefert =LINKS(A2;FINDEN(" ";A2)-1) den Vornamen und =TEIL(A2;FINDEN(" ";A2)+1;100) den Rest. In Excel 365 machen TEXTVOR und TEXTNACH dasselbe einfacher. Willst du ganz ohne Formeln auskommen, nimm Daten > Text in Spalten oder die Blitzvorschau.

Warum addiert Excel meine Zahlen nicht?

Einige davon sind wahrscheinlich als Text gespeicherte Zahlen, typischerweise aus einem Export. SUMME überspringt Text, also ist die Summe zu niedrig, ohne dass du einen Fehler bekommst. Textzahlen stehen meistens links in der Zelle und haben oft ein kleines grünes Dreieck in der Ecke. Wandle sie mit WERT, Text in Spalten oder In eine Zahl umwandeln um.

Wie finde ich Duplikate in Excel?

Schreib =ZÄHLENWENN($A$2:$A$100;A2) in eine Hilfsspalte und kopier sie nach unten. Alles über 1 ist ein Duplikat. Die bedingte Formatierung kann Duplikate hervorheben, und Daten > Duplikate entfernen löscht sie. Mach das an einer Kopie.

Soll ich Daten mit Formeln oder von Hand bereinigen?

Mit Formeln in Hilfsspalten neben den ursprünglichen Daten. Das geht schneller, sobald es mehr als ein paar Zeilen sind, du siehst genau, was sich geändert hat, und die Formeln funktionieren beim Export im nächsten Monat wieder. Füg das Ergebnis als Werte ein, wenn du zufrieden bist.

Wie heißt GLÄTTEN auf Englisch?

GLÄTTEN heißt TRIM, GROSS2 heißt PROPER, WECHSELN heißt SUBSTITUTE, TEXTVERKETTEN heißt TEXTJOIN, und LINKS, RECHTS und TEIL heißen LEFT, RIGHT und MID.

Weitere Anleitungen