EAN-Prüfziffer in Excel und Google Tabellen berechnen und prüfen — fertige Formeln

Wie prüfe ich eine ganze Spalte mit EAN-Codes in Excel?

Die Codes stehen in Spalte A, ab Zeile 2. Fügen Sie die Formel in B2 ein und ziehen Sie sie nach unten: am Ausfüllkästchen (dem kleinen Quadrat unten rechts in der Zelle) ziehen oder darauf doppelklicken. Mit einem Dreifachklick markieren Sie die ganze Formel.

=WENNFEHLER(WENN(A2="";"";WENN(ISTZAHL(SUCHEN("E+";A2));"wissenschaftliche Schreibweise";WENN(ISTFEHLER(SUMMENPRODUKT(TEIL(A2;ZEILE(INDIREKT("1:"&LÄNGE(A2)));1)*1));"nicht nur Ziffern";WENN(ODER(LÄNGE(A2)=8;LÄNGE(A2)=12;LÄNGE(A2)=13;LÄNGE(A2)=14);WENN(REST(SUMMENPRODUKT(TEIL(RECHTS(WIEDERHOLEN("0";14)&A2;14);ZEILE(INDIREKT("1:14"));1)*(1+2*REST(ZEILE(INDIREKT("1:14"));2)));10)=0;"OK";"falsche Prüfziffer");"falsche Länge: "&LÄNGE(A2)))));"nicht nur Ziffern")

Ergebnis	Bedeutung
OK	8, 12, 13 oder 14 Ziffern, und die Prüfziffer stimmt, z. B. 4006381333931
falsche Prüfziffer	die letzte Ziffer folgt nicht aus den anderen — meist ist eine Ziffer vertippt, z. B. 4006381333930
falsche Länge: 11	eine andere Anzahl Ziffern; 11 ist meist ein UPC-A-Code, der seine führende Null verloren hat, z. B. 12345678905
nicht nur Ziffern	ein Leerzeichen, ein Bindestrich, ein Buchstabe, ein geschütztes Leerzeichen, ein Platzhalter wie „k. A.“ oder ein Fehler aus einer anderen Formel, z. B. 400-6381-33393
wissenschaftliche Schreibweise	der Code steht als Text wie 4,00638E+12 in der Zelle, seine letzten Ziffern sind weg

OK heißt, dass der Code richtig aufgebaut ist. Ob es die Nummer gibt, ist eine andere Frage — mehr dazu unten.

Getestet haben wir beide Formeln in Excel 2021 mit deutscher Oberfläche und in Google Tabellen mit dem Gebietsschema Deutschland, jeweils mit den Codes aus den Tabellen auf dieser Seite.

Wie berechne ich die Prüfziffer in Excel?

Diese Formel ist für Codes ohne letzte Ziffer: 7 Ziffern für EAN-8, 11 für UPC-A, 12 für EAN-13 und 13 für GTIN-14. Das Ergebnis ist Text, also macht Excel daraus weder 4,00638E+12 noch lässt es eine führende Null weg.

=WENNFEHLER(WENN(A2="";"";WENN(ISTFEHLER(SUMMENPRODUKT(TEIL(A2;ZEILE(INDIREKT("1:"&LÄNGE(A2)));1)*1));"nicht nur Ziffern";WENN(ODER(LÄNGE(A2)=7;LÄNGE(A2)=11;LÄNGE(A2)=12;LÄNGE(A2)=13);A2&REST(-SUMMENPRODUKT(TEIL(RECHTS(WIEDERHOLEN("0";13)&A2;13);ZEILE(INDIREKT("1:13"));1)*(1+2*REST(ZEILE(INDIREKT("1:13"));2)));10);"falsche Länge: "&LÄNGE(A2))));"nicht nur Ziffern")

In Spalte A	Ergebnis
400638133393	4006381333931
9638507	96385074
01234567890	012345678905
1400638133393	14006381333938

Verwenden Sie sie nur für Codes, die noch keine Prüfziffer haben. Eine vollständige EAN-13 hat auch 13 Ziffern — die Formel hängt dann eine vierzehnte an, und heraus kommt keine Nummer dieses Produkts.

Excel auf Englisch: dieselben Formeln mit englischen Funktionsnamen

Ein englisches Excel kennt die deutschen Funktionsnamen nicht. Hat es deutsche Ländereinstellungen (Komma als Dezimaltrennzeichen), fügen Sie diese Fassungen ein — englische Namen, Semikolons:

=IFERROR(IF(A2="";"";IF(ISNUMBER(SEARCH("E+";A2));"wissenschaftliche Schreibweise";IF(ISERROR(SUMPRODUCT(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)*1));"nicht nur Ziffern";IF(OR(LEN(A2)=8;LEN(A2)=12;LEN(A2)=13;LEN(A2)=14);IF(MOD(SUMPRODUCT(MID(RIGHT(REPT("0";14)&A2;14);ROW(INDIRECT("1:14"));1)*(1+2*MOD(ROW(INDIRECT("1:14"));2)));10)=0;"OK";"falsche Prüfziffer");"falsche Länge: "&LEN(A2)))));"nicht nur Ziffern")

=IFERROR(IF(A2="";"";IF(ISERROR(SUMPRODUCT(MID(A2;ROW(INDIRECT("1:"&LEN(A2)));1)*1));"nicht nur Ziffern";IF(OR(LEN(A2)=7;LEN(A2)=11;LEN(A2)=12;LEN(A2)=13);A2&MOD(-SUMPRODUCT(MID(RIGHT(REPT("0";13)&A2;13);ROW(INDIRECT("1:13"));1)*(1+2*MOD(ROW(INDIRECT("1:13"));2)));10);"falsche Länge: "&LEN(A2))));"nicht nur Ziffern")

Excel mit englischen Ländereinstellungen (Punkt als Dezimaltrennzeichen) erwartet Kommas statt Semikolons — ersetzen Sie einfach alle Semikolons, denn in den Texten in Anführungszeichen kommt keines vor. Die Fassung mit Kommas steht auch auf der englischen Seite.

Wie funktionieren die Formeln?

Beide füllen den Code zuerst vorne mit Nullen auf 14 Ziffern auf: RECHTS(WIEDERHOLEN("0";14)&A2;14). Nullen vorne ändern die Prüfziffer nicht, deshalb deckt eine einzige Formel EAN-8, UPC-A, EAN-13 und GTIN-14 ab. TEIL mit ZEILE(INDIREKT("1:14")) zerlegt den Code in einzelne Ziffern, und 1+2*REST(ZEILE(…);2) gibt ihnen ihre Gewichte: 3 an ungeraden Stellen, 1 an geraden. In einem 14-stelligen Code ist das die übliche Regel — von rechts abwechselnd 3 und 1, beginnend mit der Ziffer direkt vor der Prüfziffer. SUMMENPRODUKT multipliziert und addiert. Die Prüfziffer selbst geht mit dem Gewicht 1 in die Summe ein, also ist die Summe bei einem richtigen Code ein Vielfaches von 10 — das prüft REST(…;10)=0.

Die zweite Formel macht dasselbe mit 13 Stellen und hängt REST(-Summe;10) an: was der Summe bis zum nächsten Vielfachen von zehn fehlt, oder 0, wenn sie schon eines ist. Das Verfahren selbst, mit Rechenbeispiel, steht auf der Seite EAN prüfen.

Die Formeln sind mit Absicht lang. Matrixkonstanten (Werte in geschweiften Klammern) kommen darin nicht vor, weil Excel in ihnen je nach Sprache andere Trennzeichen erwartet. Auch LET und SEQUENZ fehlen, weil Excel 2019 und ältere Versionen diese Funktionen nicht kennen. Die neueste Funktion darin ist WENNFEHLER (seit Excel 2007), und SUMMENPRODUKT muss nicht mit Strg+Umschalt+Eingabe bestätigt werden.

Bevor Sie Codes in eine Tabelle einfügen

Excel und Google Tabellen halten einen Code für eine Zahl. Führende Nullen verschwinden, und Excel zeigt eine 13-stellige Zahl als 4,00638E+12 an. Solange die Zelle die Zahl enthält, geht nichts verloren: Die Formel liest den vollen Wert und liefert OK. Die Ziffern gehen verloren, wenn Excel die Tabelle als CSV speichert, denn dann steht in der Datei der Text 4,00638E+12. Ein solcher Code lässt sich nur aus seiner Quelle zurückholen — aus dem Shop, aus der Lieferantendatei, von der Verpackung.

Damit Codes genau so bleiben, wie sie sind, formatieren Sie die Spalte vor dem Einfügen als Text: in Excel Start → Zahlenformat → Text; in Google Tabellen Format → Zahl → Nur Text.

Was prüfen die Formeln nicht?

• Ob es die Nummer gibt und ob sie zu diesem Produkt gehört. Das lässt sich nur im GS1-Register feststellen — einen Code können Sie bei Verified by GS1 nachschlagen.
• Bereiche, die Google Merchant Center nicht annimmt: interne Codes, die mit 2, 02 oder 04 beginnen, und Gutscheincodes, die mit 05, 98 oder 99 beginnen (in der 13-stelligen Form). Bei ihnen liefert die Formel OK.
• Duplikate, auch denselben Code in zwei Schreibweisen, etwa 012345678905 und 0012345678905.
• Einen Code aus Nullen: 0000000000000 hat eine richtige Prüfziffer.
• ISBN-10, die eine eigene Prüfziffer hat und auf X enden kann. Die Formel meldet dafür falsche Länge: 10 oder nicht nur Ziffern. Eine ISBN-13 ist eine EAN-13 und wird wie eine solche geprüft.

Wie prüfe ich eine ganze Datei auf einmal?

EAN prüfen erledigt alles aus dieser Liste außer der Abfrage im GS1-Register, ohne Formeln: Spalte einfügen oder den Shop-Export als CSV oder XLSX öffnen. Das Werkzeug findet Duplikate auch in unterschiedlicher Schreibweise, nennt das unsichtbare Zeichen in einem Code und gibt zu jedem Problem die Zeilennummer an. Die Datei verlässt Ihren Browser nicht.

In Google Tabellen macht das kostenlose Add-on GTIN & EAN Check (Seite auf Englisch) dasselbe, ohne die Tabelle zu verlassen: Spalte markieren und Erweiterungen → GTIN & EAN Check → Check selected codes wählen. Die zu korrigierenden Codes landen in einem Bericht auf einem neuen Tabellenblatt, mit einem Link zu jeder Zelle.

Diese Seite wurde noch nicht von Muttersprachlern geprüft. Klingt etwas falsch oder stimmt etwas nicht? Schreiben Sie an contact@merchlint.com.