IP-Adressen sortieren – Excel & Google Sheets
In diesem Tutorial zeigen wir Ihnen, wie Sie IP-Adressen in Excel und Google Sheets sortieren können.
IP-Adressen sortieren
IP-Adressen bestehen aus vier durch Punkte getrennte Zahlen (mit einer maximalen Länge von jeweils drei Ziffern). Excel kann Daten in diesem Format nicht richtig sortieren. Sie müssen zunächst führende Nullen hinzufügen, damit alle vier Zahlen drei Zeichen lang sind. Angenommen, Sie haben die folgende Liste von IP-Adressen:
Um sie richtig zu sortieren, gehen Sie folgendermaßen vor:
- Geben Sie in Zelle C2 die folgende Formel ein, die führende Nullen zu jedem Zahlenteil der IP-Adressen hinzufügt:
=TEXT(LINKS(B1;FINDEN(".";B1;1)-1);"000")&"."&TEXT(TEIL(B1;FINDEN(".";B1;1)+1;FINDEN(".";B1;FINDEN(".";B1;1)+1)-FINDEN(".";B1;1)-1);"000")& "."&TEXT(TEIL(B1;FINDEN(".";B1;FINDEN(".";B1;1)+1)+1;FINDEN(".";B1;FINDEN(".";B1;FINDEN(".";B1;1)+1)+1)-FINDEN(".";B1;FINDEN(".";B1;1)+1)-1);"000")&"."&TEXT(RECHTS(B1;LÄNGE(B1)-FINDEN(".";B1;FINDEN(".";B1;FINDEN(".";B1;1)+1)+1));"000")
- Ziehen Sie die Formel nach unten bis zur die letzten ausgefüllten Zeile (6).
- Jetzt haben alle Zahlen in den IP-Adressen die notwendigen führenden Nullen. Kopieren Sie vor dem Sortieren die Werte und fügen Sie sie ein. Markieren Sie den Bereich mit den Formeln (C1:C6), klicken Sie mit der rechten Maustaste auf diesen markierten Bereich und wählen Sie Kopieren (oder verwenden Sie die Tastenkombination STRG+C).
- Klicken Sie mit der rechten Maustaste auf die erste Zelle in der nächsten Spalte (D1), und wählen Sie das Symbol Werte einfügen (oder verwenden Sie den Tastenkombination zu Werte einfügen).
- Löschen Sie die Spalte mit den Formeln und sortieren Sie die formatierten Daten. Klicken Sie irgendwo in den formatierten Datenbereich (Spalte C), und gehen Sie in der Menüleiste auf Start > Sortieren und Filtern > Von A bis Z sortieren.
Daraufhin werden die IP-Adressen in Spalte B richtig sortiert, und Sie können die Hilfsspalte (C) löschen.
Wie funktioniert die Formel?
Die komplexe Formel aus Schritt 1 verwendet die Funktionen TEXT, LINKS, TEIL, RECHTS und FINDEN, um führende Nullen hinzuzufügen, damit jede der vier Zahlen eine Länge von drei hat.
- Die FINDEN-Funktion findet Punkte in der IP-Adresse.
- Die Funktionen LINKS, TEIL und RECHTS extrahieren jede Zahl.
- Schließlich formatiert die Funktion TEXT jede Zahl so, dass sie eine Länge von drei Ziffern hat („000“). Das bedeutet, dass bei einer ein- oder zweistelligen Zahl zwei bzw. eine führende Nullen hinzugefügt werden. Das kaufmännische Und (&) wird verwendet, um alle durch Punkte getrennten Zahlen zu verbinden.
IP-Adressen in Google Sheets sortieren
Mit der gleichen Formel wie in Schritt 1 oben können Sie auch IP-Adressen in Google Sheets sortieren.
- Geben Sie in Zelle C2 die Formel ein und ziehen Sie sie in die letzte ausgefüllte Zeile (6).
=TEXT(LINKS(B1;FINDEN(".";B1;1)-1);"000")&"."&TEXT(TEIL(B1;FINDEN(".";B1;1)+1;FINDEN(".";B1;FINDEN(".";B1;1)+1)-FINDEN(".";B1;1)-1);"000")& "."&TEXT(TEIL(B1;FINDEN(".";B1;FINDEN(".";B1;1)+1)+1;FINDEN(".";B1;FINDEN(".";B1;FINDEN(".";B1;1)+1)+1)-FINDEN(".";B1;FINDEN(".";B1;1)+1)-1);"000")&"."&TEXT(RECHTS(B1;LÄNGE(B1)-FINDEN(".";B1;FINDEN(".";B1;FINDEN(".";B1;1)+1)+1));"000")- Jetzt haben alle IP-Adressnummern führende Nullen mit jeweils einer Länge von drei. Kopieren Sie vor dem Sortieren die Werte und fügen Sie sie ein. Wählen Sie den Bereich mit den Formeln (C1:C6), klicken Sie mit der rechten Maustaste auf den ausgewählten Bereich und wählen Sie Kopieren (oder verwenden Sie STRG+C).
- Klicken Sie mit der rechten Maustaste auf die erste Zelle in der nächsten Spalte (D1), klicken Sie auf Inhalte einfügen und wählen Sie Nur Werte (oder verwenden Sie die Tastenkombination STRG+UMSCHALT+V).
- Löschen Sie die Spalte mit den Formeln und sortieren Sie die formatierten Daten. Klicken Sie irgendwo in den formatierten Datenbereich (Spalte C), und wählen Sie im Menü Daten > Tabellenblatt sortieren > Tabellenblatt nach Spalte C sortieren (A bis Z).
Daraufhin werden die IP-Adressen in Spalte B richtig sortiert, und Sie können die Hilfsspalte (C) löschen.
