Wie erhalte ich Zellen in Excel, die IP-Adressen enthalten, um richtig zu sortieren?

Question

Ich arbeite derzeit mit einer großen Liste von IP-Adressen (Tausende von ihnen).

Wenn ich die Spalte mit den IP-Adressen sortiere, werden sie jedoch nicht auf intuitive oder leicht verständliche Weise sortiert.

Wenn ich zum Beispiel IP-Adressen wie folgt eingebe:

Und wenn ich dann in aufsteigender Reihenfolge sortiere, erhalte ich Folgendes:

Kann ich die Zellen so formatieren, dass beispielsweise eine IP-Adresse von 17.255.253.65 after 1.128.96.254 und before 103.236.162.56 in aufsteigender Reihenfolge angezeigt wird? ?

Wenn nicht, gibt es einen anderen Weg für mich, um dieses ultimative Ziel zu erreichen?

Blackwood · Accepted Answer

Wie Sie vielleicht bemerkt haben, werden Ihre IP-Adressen als Text und nicht als Zahlen behandelt. Sie werden als Text sortiert, dh Adressen, die mit "162" beginnen, stehen vor Adressen, die mit "20" beginnen. (weil das Zeichen "1" vor dem Zeichen "2" steht.

Sie können die in dieser Antwort angegebene Formel verwenden: https://stackoverflow.com/a/31615838/4424957 , um die IP-Adresse in ihre Teile aufzuteilen.

Wenn sich Ihre IP-Adressen in den Spalten A befinden, fügen Sie die Spalten B-E wie unten gezeigt hinzu.

Geben Sie die Formel ein

=VALUE(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",999)),(B$1)*999-998,999)))

in Zelle B2 und kopieren Sie es in die Spalten B-E in allen Zeilen, um die vier Teile jeder IP-Adresse abzurufen. Sortieren Sie nun den gesamten Bereich nach den Spalten B bis E (in dieser Reihenfolge), wie unten gezeigt:

Wenn Sie die Hilfsspalten (B-E) nicht sehen möchten, können Sie sie ausblenden.

Rajesh S · Answer

Die einfachste Lösung in 3 Schritten, die ich vorschlagen kann, dass Sie es sind ,

Wählen Sie die IP-Adressenspalte aus und wenden Sie den Befehl Text in Spalte an.
In der nebenstehenden Spalte schreiben Sie diese Formel

= KONKATENAT (B3, ".", C3, ".", D3, ".", E3)
Zuletzt in aufsteigender Reihenfolge sortieren.

Überprüfen Sie den Screenshot.

NB:

Rot ist die ursprüngliche IP-Adresse (in Spalte A).

Grün nach Anwenden von Text auf Spalte (Spalte B bis E).

Schwarz wird nach dem Anwenden von Verketten und Sortieren (Spalte F) angezeigt.

Der Grund dafür ist sehr einfach, dass die ursprüngliche IP-Adresse Textdaten ist und Excel kein Zellenformat akzeptiert, um sie in Zahl umzuwandeln.

Hoffe das hilft dir.

grahamj42 · Answer

Hier ist eine VBA-Funktion, die ich vor einiger Zeit geschrieben habe, um das gleiche Problem zu lösen. Es wird eine gepolsterte Version einer IPv4-Adresse generiert, die korrekt sortiert ist.

Function SortAddress(Address As String) ' format address as XXX.XXX.XXX.XXX to permit sorting Dim FirstByte As Integer, LastByte As Integer, I As Integer SortAddress = "" FirstByte = 1 For I = 0 To 2 ' process the first three bytes LastByte = InStr(FirstByte, Address, ".") ' find the dot ' append the byte as 3 digits followed by dot SortAddress = SortAddress & Format(Mid(Address, FirstByte, LastByte - FirstByte), "000\.") FirstByte = LastByte + 1 ' shift the start pointer Next I SortAddress = SortAddress & Format(Mid(Address, FirstByte), "000") ' process the last byte End Function

Einfaches Beispiel:

Ergebnis

Formeln

Sie können nach der Spalte "Sortierbar" sortieren und diese ausblenden.

RudyB · Answer

Hier ist eine Antwort, die nur eine Spalte Ihrer Tabelle benötigt und die IPv4-Adresse in eine 10er-Basis-Nummerierung umwandelt.

Da Sie Ihre Daten in Spalte "M" einfügen, beginnt dies in Zelle M2 (M1 ist die Bezeichnung). Das Einkapseln als Code führt zu einem furchtbaren Durcheinander. Daher habe ich blockquote verwendet:

= INT (LINKS (M2, SUCHEN (".", M2) - 1)) * 256 ^ 3 + INT (MITTEL (M2, SUCHEN (".", M2) + 1, SUCHEN (".", M2, SUCHEN (".", M2) + 1) - FIND (".", M2) -1)) * 256 ^ 2 + INT (MID (M2, FIND (".", M2, FIND (".", M2) + 1) + 1, SUCHEN (".", M2, SUCHEN (".", M2, SUCHEN (".", M2) + 1) + 1) - SUCHEN (".", M2, SUCHEN ("." , M2) + 1) - 1)) * 256 + INT (RECHTS (M2, LEN (M2) - FIND (".", M2, FIND (".", M2, FIND (".", M2) + 1 ) + 1)))

Nicht gerade die am leichtesten lesbare Formel, aber Sie können sie einfach kopieren und in Ihre Zelle einfügen (vorzugsweise N2 oder etwas anderes in derselben Zeile wie Ihre erste IP-Adresse). Die korrekte Formatierung der IP-Adresse wird vorausgesetzt, da eine Fehlerkorrektur in der Formel das Parsen durch den Menschen noch verschlimmern würde.

user856853 · Answer

Dies ist ein ähnlicher Einzeiler, der die Oktette in dreistellige Felder umwandelt, die eine ordnungsgemäße Sortierung ermöglichen.

10.1.0.15 wird zu 10001000015.

=LEFT(B85, FIND(".", B85) - 1) * 1000000000 + MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 1)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - FIND(".", B85) - 1) * 1000000 + MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 3)) - FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - 1) * 1000 + RIGHT(B85, LEN(B85) - FIND("x", SUBSTITUTE(B85, ".", "x", 3)))

click here · Answer

Wenn Sie keine Formeln oder VBA verwenden möchten, verwenden Sie Power Query. (In Excel 2016, Get & Transform, in Excel 2010 oder 2013 installieren Sie das PowerQuery-Add-In, um mitzumachen.).

Bringen Sie die Tabelle in den PowerQuery-Editor.
Duplizieren Sie die Spalte, indem Sie mit der rechten Maustaste auf "Spalte duplizieren" klicken.
"Spalte teilen" nach Trennzeichen auf der Registerkarte "Startseite". Wählen Sie "Jedes Vorkommen des Begrenzers"
Sortieren Sie jede Spalte aufsteigend. von links nach rechts.
Wählen Sie die zuvor geteilten Spalten aus, klicken Sie mit der rechten Maustaste und entfernen Sie sie, schließen Sie sie und laden Sie sie.

wilson · Answer

Wie in Frage gezeigt, sind Spalte M die IP-Adressen (IPv4), beginnend mit M2.

Hier ist meine Lösung, indem ich die guten Punkte aus der Antwort aller heraushole. Es wird nur 1 Hilfssäule benötigt. Wir versuchen, die IPv4-Adressen in das Format 012.198.043.009 zu formatieren und sie dann zu sortieren:

12.198.43.9 ZU 12 198 43 9, DANN ZU 012.198.043.009

Formatieren Sie IPv4-Adressen in das 012.198.043.009 -Format, indem Sie N2 eingeben und abwärts ausfüllen:

= TEXT( LEFT(SUBSTITUTE(M2, ".", " "), 3 ), "000") & "." & TEXT( MID(SUBSTITUTE(M2, ".", " "), 8, 5 ), "000") & "." & TEXT( MID(SUBSTITUTE(M2, ".", " "), 15, 7), "000") & "." & TEXT(RIGHT(SUBSTITUTE(M2, ".", " "), 3 ), "000")

Sortieren nach Spalte N

Erklärung

Durch SUBSTITUTE des Punktes . mit 6 Leerzeichen erhalten wir Folgendes, damit sie korrekt extrahiert werden können:

 |123456789|123546789|123456789| 1.1.1.1 -> 1 1 1 1 11.11.11.11 -> 11 11 11 11 111.111.111.111 -> 111 111 111 111 =1= ==2== ===3===

Zeichen 1-3 enthält und enthält nur den ersten Teil.
Zeichen 8-12 enthält und enthält nur den zweiten Teil.
Zeichen 15-21 enthält und enthält nur den dritten Teil.
Ganz rechts 3 Zeichen enthält und enthält nur den vierten Teil.

Extrahieren und formatieren Sie dann jeden Teil mit TEXT(..., "000").