Chuck hat ein Arbeitsblatt, das in einer Spalte eine Reihe von IP-Adressen enthält. Diese haben das bekannte Format 192.168.2.1. Wenn er die Adressen sortiert, werden sie nicht numerisch sortiert. Beispielsweise platziert Excel 192.168.1.100 zwischen 192.168.1.1 und 192.168.1.2. Chuck fragt sich, ob es eine Möglichkeit gibt, eine Spalte mit IP-Adressen so zu sortieren, dass sie in der richtigen Reihenfolge angezeigt werden.

Dies liegt daran, dass Excel eine IP-Adresse als Text und nicht als Zahl oder Folge von Zahlen ansieht. Es gibt einige Möglichkeiten, wie Sie das Problem umgehen können. Einige davon werde ich in diesem Tipp erläutern. Sie sollten den Ansatz wählen, der Ihren Anforderungen entspricht, wie er durch Ihre Daten definiert ist und wie Sie diese Daten verwenden.

Ein Ansatz besteht darin, sicherzustellen, dass jedes Oktett Ihrer IP-Adressen aus drei Ziffern besteht. (Ein Oktett ist jeder Teil der IP-Adresse, getrennt durch Punkte.) Anstelle einer Adresse wie 192.168.1.1 würden Sie beispielsweise 192.168.001.001 verwenden. Diese „Frontpads“ jedes Oktett mit Nullen und wenn alle Ihre IP-Adressen in diesem Format sind, werden sie korrekt sortiert.

Wenn Sie es vorziehen, eine Formel zu verwenden, um die Frontauffüllung jedes Oktetts sicherzustellen, können Sie Folgendes verwenden:

=TEXT(LEFT(A1,FIND(".",A1,1)-1),"000") & "." & TEXT(MID(A1,FIND( ".",A1,1)+1,FIND(".",A1,FIND(".",A1,1)+1)-FIND(".",A1,1)-1),"000")

& "." & TEXT(MID(A1,FIND(".",A1,FIND(".",A1,1)+1)+1,FIND(".",A1, FIND(".",A1,FIND(".",A1,1)+1)+1)-FIND(".",A1,FIND(".",A1,1)+1)-1), "000") & "." & TEXT(RIGHT(A1,LEN(A1)-FIND(".",A1,FIND(".",A1,FIND( ".",A1,1)+1)+1)),"000")

Diese Formel ist ziemlich lang, aber es ist immer noch eine einzelne Formel. Platzieren Sie es in der Spalte neben Ihren ersten IP-Adressen (vorausgesetzt, die Adresse befindet sich in Zelle A1) und kopieren Sie es dann in so viele Zeilen wie erforderlich. Wenn Sie sortieren, sortieren Sie nach Spalte B, und die Adressen werden in der richtigen Reihenfolge angezeigt.

Wenn Sie mit vielen IP-Adressen arbeiten, möchten Sie möglicherweise eine benutzerdefinierte Funktion erstellen, die jedes Oktett der IP-Adresse mit Nullen auffüllt und dann eine vollständig formatierte IP zurückgibt. Folgendes wird die Aufgabe ausführen:

Function FormatIP(IPAddr As String) As String     Dim Dot1 As Integer     Dim Dot2 As Integer     Dim Dot3 As Integer     Dim Octet1 As String     Dim Octet2 As String     Dim Octet3 As String     Dim Octet4 As String

Dot1 = InStr(1, IPAddr, ".", vbTextCompare)

Dot2 = InStr(Dot1 + 1, IPAddr, ".", vbTextCompare)

Dot3 = InStr(Dot2 + 1, IPAddr, ".", vbTextCompare)



Octet1 = Left(IPAddr, Dot1-1)

Octet2 = Mid(IPAddr, Dot1+1, Dot2-Dot1-1)

Octet3 = Mid(IPAddr, Dot2+1, Dot3-Dot2-1)

Octet4 = Mid(IPAddr, Dot3+1, Len(IPAddr))



FormatIP = Right("000" & Octet1, 3) & "."

FormatIP = FormatIP & Right("000" & Octet2, 3) & "."

FormatIP = FormatIP & Right("000" & Octet3, 3) & "."

FormatIP = FormatIP & Right("000" & Octet4, 3)

End Function

In Excel können Sie die UDF dann auf diese Weise verwenden, indem Sie Ihre ursprüngliche IP-Adresse in Zelle A1 annehmen:

=FormatIP(A1)

Ein anderer Ansatz besteht darin, die IP-Adressen einfach in separate Spalten zu unterteilen und jedes Oktett in eine eigene Spalte zu setzen. Dies ist einfach, wenn Sie das Werkzeug Text in Spalten folgendermaßen verwenden:

  1. Stellen Sie sicher, dass sich rechts von Ihren IP-Adressen drei leere Spalten befinden.

  2. Wählen Sie den Zellenbereich aus, der die IP-Adressen enthält.

  3. Zeigen Sie die Registerkarte Daten des Menübands an.

  4. Klicken Sie in der Gruppe Datenwerkzeuge auf das Werkzeug Text in Spalten. Excel startet den Assistenten zum Konvertieren von Text in Spalten. (Siehe Abbildung 1.)

  5. Aktivieren Sie das Optionsfeld Begrenzt.

  6. Klicken Sie auf Weiter.

  7. Aktivieren Sie das Kontrollkästchen neben Andere und geben Sie rechts neben dem Kontrollkästchen einen Punkt ein.

  8. Klicken Sie auf Fertig stellen.

Anschließend können Sie die vier Spalten wie gewohnt sortieren. Wenn Sie dann die IP-Adressen wieder zusammensetzen möchten, können Sie eine Formel wie die folgende verwenden:

=A1 & "." & B1 & "." & C1 & "." & D1

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (13481) gilt für Microsoft Excel 2007, 2010, 2013 und 2016.