Nummern wie 1, 1a, 2, 10 in Excel richtig sortieren

Positionsnummern, Hausnummern oder Artikelnummern bestehen oft aus einer Zahl mit Zusatz: 1, 1a, 1b, 2, 10, 11a. Sortiert Excel diese Liste, stimmt die Reihenfolge nicht. Reine Zahlen stehen vor dem Text, und „10“ kommt als Text vor „2“, weil Excel Zeichen für Zeichen vergleicht.

Die Kurzlösung

Zwei Hilfsspalten trennen die Nummer in Zahl und Zusatz. Steht die Nummer in A1:

Zahl in B1:

=--LINKS(A1;VERGLEICH(WAHR;ISTFEHLER(--TEIL(A1&"x";SEQUENZ(LÄNGE(A1)+1);1));0)-1)

Zusatz in C1:

=TEIL(A1;VERGLEICH(WAHR;ISTFEHLER(--TEIL(A1&"x";SEQUENZ(LÄNGE(A1)+1);1));0);99)

Formeln nach unten kopieren, dann über Daten > Sortieren erst nach Spalte B, dann nach Spalte C sortieren. In Microsoft 365 geht es auch ohne Umsortieren der Liste:

=SORTIERENNACH(A1:A13;B1:B13;1;C1:C13;1)

So funktionieren die Formeln

TEIL zerlegt die Nummer in einzelne Zeichen, SEQUENZ liefert dafür die Positionen 1, 2, 3 und so weiter. Das doppelte Minus versucht, jedes Zeichen in eine Zahl umzuwandeln. Bei Buchstaben entsteht ein Fehler. VERGLEICH findet die Position des ersten Fehlers, also das erste Zeichen, das keine Ziffer ist. Das angehängte „x“ sorgt dafür, dass es auch bei reinen Zahlen einen solchen Fehler gibt.

Alles links davon ist die Zahl, alles ab dieser Position der Zusatz. Damit sortiert die Lösung so:

VorherNachher
10, 1b, 2, 11a, 1, 1a, 11, 9, 1c, 11b, 100, 1A, 10ab1, 1a, 1A, 1b, 1c, 2, 9, 10, 10ab, 11, 11a, 11b, 100

Warum die alte Formel nicht reicht

Häufig findet man diese Formel:

=WENN(ISTZAHL(A1);A1;LINKS(A1;LÄNGE(A1)-1)+CODE(RECHTS(A1;1))/1000)

Sie rechnet den letzten Buchstaben als Tausendstel dazu. Das funktioniert nur bei genau einem Buchstaben. So landet „10ab“ hinter „100“, und „1A“ steht vor „1a“, weil Großbuchstaben einen kleineren Zeichencode haben.

Für Excel 2016 und 2019

SEQUENZ gibt es erst ab Excel 2021. In älteren Versionen ersetzen Sie SEQUENZ(LÄNGE(A1)+1) durch ZEILE(INDIREKT("1:"&LÄNGE(A1)+1)) und schließen die Formel mit Strg+Umschalt+Eingabe ab. Sortiert wird dann über Daten > Sortieren.

Typische Fehler

  • Nummer beginnt mit Buchstaben: Bei „A12“ findet die Formel keine Zahl am Anfang und meldet #WERT!. Für solche Nummern trennt man den Buchstaben vorne ab, die Logik bleibt dieselbe.
  • Führende Nullen: „007“ und „7“ haben dieselbe Zahl und werden gleich einsortiert. Soll „007“ vorne stehen, sortieren Sie zusätzlich nach der Länge.
  • Leerzeichen: „11 a“ ergibt den Zusatz „ a“ mit Leerzeichen. GLÄTTEN um den Zusatz behebt das.

Passende Beiträge

Lernen oder machen lassen?

Excel-Formeln, die im Alltag sitzen.

In einer Schulung arbeiten wir mit Ihren eigenen Dateien, damit Sie das Gelernte am nächsten Tag nutzen. Oder wir bauen die Lösung direkt für Sie.