Excel-Export und -Import: Nullen, Artikelnummern, Zahlenformate

Wer Artikelnummern, GTIN oder Kontonummern in einer Tabellenkalkulation pflegt, arbeitet mit einem Programm, das eine Ziffernfolge standardmäßig zuerst als Zahl liest. Solange Sie rechnen, ist das richtig; sobald die Ziffern eine Kennung sind, wird jede Umdeutung zum Datenfehler. Die folgenden Schritte halten Kennungen beim Export und beim Import zusammen.

Was Sie vorab brauchen

• eine Liste der Spalten mit Kennungen: Artikelnummer, GTIN, Chargen-, Konten- oder Personalnummer, Postleitzahl
• eine unveränderte Kopie der Quelldatei, auf der Sie arbeiten; das Original bleibt unberührt
• eine Vorgabe je Spalte: Datentyp, Stellenzahl, erlaubte Zeichen, Schreibweise für Datum und Dezimalzeichen
• in Excel für Microsoft 365 und in Excel 2024 den Zugang zu Optionen > Daten > Automatische Datenkonvertierung (macOS: Excel > Einstellungen > Bearbeiten)
• ein Testschema oder ein Testmandant Ihres ERP für den Import
• Prüfgrößen für den Abgleich: Zeilenzahl, Feldlänge, Prüfziffer, Kontrollsumme

Was Excel beim Einlesen verändert

Excel entscheidet beim Einlesen standardmäßig selbst, ob ein Wert eine Zahl, ein Datum oder Text ist; die vier Konvertierungen unten lassen sich in Excel für Microsoft 365 und in Excel 2024 einzeln abschalten. Beim Öffnen einer CSV-Datei verwendet Excel die aktuellen Standard-Datenformate, um jede Spalte zu interpretieren; mehr Kontrolle geben der Import über Daten > Aus Text/CSV (Power Query) und der Textimport-Assistent (Microsoft: Import or export text files).

Vier Konvertierungen beschreibt Microsoft ausdrücklich:

1. Führende Nullen einer Ziffernfolge werden entfernt; aus 00123 wird die Zahl 123.
2. Ziffernfolgen mit 16 oder mehr Stellen werden auf 15 Stellen Genauigkeit gekürzt und als Zahl angezeigt; aus 12345678901234567890 wird 12345678901234500000, dargestellt als 1,23457E+19.
3. Ziffernfolgen mit dem Buchstaben E werden in wissenschaftliche Schreibweise umgerechnet; aus 123E5 wird 1,23E+07.
4. Werte aus Buchstaben und Ziffern werden als Datum gelesen; aus JAN1 wird der 1. Januar. Werte mit Leerzeichen oder Bindestrich wie JAN 1 können weiterhin als Datum behandelt werden.

Dazu kommen zwei weitere Wege in den falschen Typ. Wer eine Ziffernfolge mit Schrägstrich oder Bindestrich eintippt, kann ein Datumsformat erhalten; dagegen hilft das Textformat (Microsoft: Format numbers). Und die Standardformatierung Allgemein zeigt Zahlen ab 12 Stellen in exponentieller Schreibweise (Microsoft: Available number formats in Excel).

Zwei Grenzen sind nicht umkehrbar. Die Zahlengenauigkeit endet bei 15 Stellen (Microsoft: Excel specifications and limits), und ein Datumswert wird intern als Seriennummer geführt, im Standardsystem ab dem 1. Januar 1900 (Microsoft: Date systems in Excel). Ein Zellformat ändert den gespeicherten Wert nicht, sondern nur die Anzeige; bei Zahlen mit mehr als 15 Stellen kann der in der Bearbeitungsleiste angezeigte Wert bereits von der Eingabe abweichen (Microsoft: Display numbers in scientific notation). Was einmal als Zahl gespeichert wurde, kommt deshalb durch keine Formatierung als Text mit Nullen zurück.

Verfälschungen, Vorbeugung und Reparatur

Die Tabelle nennt je Fall das Symptom in der Zelle, die Vorbeugung und die Reparatur; Neu einlesen heißt: unveränderte Quelldatei öffnen und die Spalte vorher als Text festlegen.

Verfälschung	Symptom in der Zelle	Vorbeugung	Reparatur
Führende Nullen entfernt	aus 00123 wird 123	Spalte vor dem Einlesen als Text formatieren	neu einlesen; das Zahlenformat 00000 wirkt nur auf später eingegebene Werte
Stellen ab der 16. werden zu Null	aus 12345678901234567890 wird 12345678901234500000	Spalte als Text, Import mit Spaltentyp Text	nur über die Quelldatei; die Ziffern ab der 16. Stelle sind verloren
Große Zahl in exponentieller Anzeige	statt der Ziffern steht eine Zahl mit E+ in der Zelle	Spalte als Text oder als Zahl mit 0 Dezimalstellen formatieren	bei höchstens 15 Stellen genügt das Format, der gespeicherte Wert ist unverändert; bei 16 oder mehr Stellen ist er bereits auf 15 Stellen gekürzt, dann die Quelldatei mit Spaltentyp Text neu einlesen
Ziffernfolge wird Datum	aus 1-2 oder JAN1 wird ein Datum	Spalte als Text formatieren und mit Spaltentyp Text einlesen; das Abschalten der automatischen Datenkonvertierung erfasst nur zusammenhängende Buchstaben-Ziffernfolgen wie JAN1, nicht JAN 1 oder JAN-1	Als Text neu eingeben oder neu einlesen; das Datum trägt die Ursprungsziffern nicht mehr
Kennung bleibt Text	Wert steht linksbündig, eine Formel zeigt statt des Ergebnisses den Formeltext	Text nur für Kennungen verwenden	Soll damit gerechnet werden, die Werte ausdrücklich in Zahlen umwandeln (In Zahl umwandeln oder die Funktion WERT); ein Zahlenformat allein ändert den gespeicherten Typ nicht und kürzt nichts, erst die Umwandlung und Speicherung als Zahl kürzt einen Wert mit 16 oder mehr Stellen auf 15 Stellen
Spalten verschoben	eine Spalte mehr als in der Kopfzeile	Trennzeichen und Dezimalzeichen prüfen; Felder mit Komma, Anführungszeichen oder Zeilenumbruch in doppelte Anführungszeichen setzen	Trennzeichen beim Import festlegen und die Kopfzeile gegenprüfen

Schritt für Schritt: Kennungen durch Excel bringen

Schritt 1: Spaltenliste und Regelwerk anlegen

Notieren Sie für jede Spalte die Kennung, ihre Stellenzahl und den Datentyp. Eine Artikelnummer mit führenden Nullen und eine GTIN sind Text, ein Preis und eine Menge sind Zahlen. Endstand: eine Liste mit einer Zeile je Spalte, die Sie beim Import abarbeiten.

Schritt 2: Die Zellen vor dem Füllen als Text formatieren

Wählen Sie die Spalte, drücken Sie Strg+1 und wählen Sie im Register Zahlen die Kategorie Text. Die Reihenfolge entscheidet: Das Textformat wirkt nur auf Werte, die danach eingegeben oder eingelesen werden; bereits eingelesene Zahlen korrigiert es nicht (Microsoft: Keeping leading zeros and large numbers). Endstand: Die Eingabe 00123 bleibt 00123.

Schritt 3: Die Datei kontrolliert einlesen

Öffnen Sie die CSV-Datei nicht per Doppelklick. Nutzen Sie Daten > Aus Text/CSV und setzen Sie im Editor die betroffenen Spalten auf den Datentyp Text, oder benennen Sie die Datei vor dem Öffnen von .csv in .txt um; dann startet der Textimport-Assistent, in dem Sie je Spalte ein Format wählen (Microsoft: Import or export text files). Endstand: Die Vorschau zeigt die Kennung mit allen Stellen und die Kopfzeile in der ersten Zeile.

Schritt 4: Die automatische Datenkonvertierung abschalten

In Excel für Microsoft 365 und in Excel 2024 (Windows und macOS) lassen sich die vier Konvertierungen einzeln abschalten: Datei > Optionen > Daten > Automatische Datenkonvertierung. Microsoft führt die Option für diese Versionen; für Excel 2016 bis 2021 ist sie nicht dokumentiert, dort bleiben das Textformat und der Import über Power Query. Auf Daten, die über Power Query geladen werden, wirken die Optionen nicht unmittelbar, weil Power Query eigene Angaben zum Datentyp hat. Das Abschalten erfasst zudem nur zusammenhängende Buchstaben-Ziffernfolgen; Werte mit Leerzeichen oder Bindestrich wie JAN 1 oder JAN-1 können weiterhin als Datum gelesen werden (Microsoft: Set automatic data conversions). Endstand: Ein eingefügter Wert wie 00123 bleibt als Text stehen.

Schritt 5: Beim Export Kennungen als Text schreiben

Damit eine Kennung als Text in der Datei landet, muss die Zelle Text enthalten: Textformat vor dem Füllen, das Apostroph vor der Ziffernfolge oder die Funktion TEXT erzeugen Text, kein Zahlenformat (Microsoft: Keeping leading zeros and large numbers). Für die Datei selbst gilt RFC 4180: Jedes Feld darf in doppelte Anführungszeichen gesetzt werden, Felder mit Komma, Anführungszeichen oder Zeilenumbruch müssen es sogar; das letzte Feld einer Zeile darf nicht mit einem Komma enden. RFC 4180 ist eine informative Denkschrift der IETF; verbindlich wird das Format erst, wenn ein Geschäftspartner oder Ihr ERP es in der Schnittstellenbeschreibung verlangt. Endstand: In der gespeicherten Datei stehen die Kennung vollständig und die Kopfzeile als erste Zeile; prüfen Sie beides in einem Texteditor.

Schritt 6: Datum und Trennzeichen festlegen

Ein Datum in einer Textdatei hat keinen Typ; Excel deutet es beim Öffnen nach den regionalen Einstellungen. Eine Spalte im Format Monat-Tag-Jahr kann daher anders gelesen werden als eine im Format Jahr-Monat-Tag (Microsoft: Import or export text files). Unsere Empfehlung: Schreiben Sie Daten in der Reihenfolge Jahr-Monat-Tag und legen Sie das Format in der Exportspezifikation fest. Beim Trennzeichen folgt Excel den Systemeinstellungen: Standardmäßig ist das Komma das Listentrennzeichen; mit dem Dezimaltrennzeichen Komma verwendet Excel das Semikolon (ebenda). Endstand: Die Datei hat ein Trennzeichen und ein Datumsformat, beide sind notiert.

Schritt 7: Nach dem Import gegenprüfen

Vergleichen Sie Zeilen- und Spaltenzahl mit der Quelldatei, die Stellenzahl jeder Kennung und eine Kontrollsumme über eine Betragsspalte. Rechnen Sie die Prüfziffer der GTIN nach, statt sie zu übernehmen. Endstand: Jede Abweichung ist entweder erklärt oder korrigiert.

Die Prüfziffer einer GTIN nachrechnen

Die Artikelnummer bauen Sie selbst auf; die GTIN beziehen Sie von Ihrer GS1-Mitgliedsorganisation (in Deutschland GS1 Germany, in Österreich GS1 Austria, in der Schweiz GS1 Switzerland). Nach GS1 Germany ist die GTIN in Deutschland und weltweit in der Regel 13-stellig; für kleine Artikel stellt GS1 unter bestimmten Voraussetzungen eine 8-stellige Kurznummer bereit, und die 14-stelligen Versionen werden auf Basis der 13-stelligen GTIN gebildet. GS1 Austria nennt vier Varianten mit 8, 12, 13 und 14 Stellen, GS1 Switzerland beschreibt die GTIN ebenfalls als 13-stellig. Die letzte Stelle ist die Prüfziffer; sie bestätigt nach GS1 die Korrektheit der Nummer.

Gerechnet wird nach dem Prüfziffern-Algorithmus des GS1-Systems, den GS1 Germany als PDF veröffentlicht: Multiplizieren Sie die Ziffern ohne Prüfziffer von rechts nach links abwechselnd mit 3 und 1, addieren Sie die Produkte und bilden Sie die Differenz der Summe zum nächsten vollen Zehner (Aufrundung). Ist die Summe durch zehn teilbar, lautet die Prüfziffer 0. Beispielrechnung mit erfundenen Ziffern: Die zwölf Stellen 4 0 1 2 3 4 5 6 7 8 9 0 ergeben die Produkte 4, 0, 1, 6, 3, 12, 5, 18, 7, 24, 9 und 0, zusammen 89. Die Summe 89 wird auf den nächsten vollen Zehner 90 aufgerundet, die Prüfziffer ist also 1; die GTIN-13 lautet 4012345678901.

So prüfen Sie das Ergebnis

1. Stellenzahl: Zählen Sie die Zeichen jeder Kennung und vergleichen Sie mit der Vorgabe aus Schritt 1.
2. Nullen: Zählen Sie die Kennungen, die mit einer Null beginnen, in Quelldatei und Zieltabelle; die Zahlen müssen gleich sein.
3. Prüfziffer: Rechnen Sie mindestens eine GTIN je Datenlieferung nach. Bilden Sie die Produktsumme über alle Stellen einschließlich Prüfziffer mit den Faktoren 1 und 3 von rechts nach links, muss sie auf eine volle Zehnerstelle aufgehen.
4. Umfang: Vergleichen Sie Zeilen- und Spaltenzahl beider Dateien.
5. Kontrollsumme: Bilden Sie über eine Betragsspalte in beiden Dateien dieselbe Summe und vergleichen Sie sie.
6. Stichprobe: Lesen Sie die erste und die letzte Zeile im Texteditor und suchen Sie Kennungen, die als Datum erscheinen können.

Wenn etwas nicht klappt

Symptom	Mögliche Ursache	Abhilfe
Die Kennung hat weniger Stellen als in der Quelldatei	Die Zelle wurde als Zahl eingelesen, führende Nullen sind entfallen	Spalte als Text formatieren und die Quelldatei neu einlesen
Am Ende stehen Nullen statt Ziffern	Die Nummer hatte 16 oder mehr Stellen und wurde auf 15 Stellen gekürzt	Quelldatei mit Spaltentyp Text neu einlesen; den gekürzten Wert verwerfen
In der Zelle steht eine Zahl mit E+	Standardformatierung Allgemein für Zahlen ab 12 Stellen	Spalte als Text oder als Zahl mit 0 Dezimalstellen formatieren; bei 16 oder mehr Stellen ist der gespeicherte Wert bereits gekürzt, dann die Quelldatei mit Spaltentyp Text neu einlesen
Aus der Nummer ist ein Datum geworden	Schrägstrich oder Bindestrich; Wert aus Buchstaben und Ziffern	Als Text neu eingeben oder neu einlesen; das Zellformat allein ändert den Wert nicht
Beim Import sind die Spalten verschoben	Anderes Listen- oder Dezimaltrennzeichen als beim Export	Trennzeichen beim Import einstellen und die Kopfzeile gegenprüfen
Das ERP zeigt nach dem Import andere Nummern	Der Import hat den Datentyp selbst bestimmt	Feldtypen in der Importspezifikation setzen und den Import im Testschema wiederholen

Wann Sie Hilfe holen sollten

Fragen Sie den Hersteller Ihres ERP, welche Feldtypen seine Importschnittstelle kennt und ob der Export den angezeigten oder den gespeicherten Wert schreibt; für den Weg zwischen Systemen siehe CSV-Austausch mit dem ERP. Für dauerhafte Prüfregeln siehe Stammdatenqualität messen, für den Ablauf einer Übernahme Datenmigration. Für Tabellen, die selbst als Grundlage von Buchungen dienen, siehe Excel-Tabellen als Buchungsgrundlage.