Tabellenkalkulation

Zellen formatieren für die Sortierung von IP-Adressen

Diskussionsgruppen
Betriebsysteme: Windows 7; WindowsVista; WindowsXP; Linux; BS-Sonstige
Software: Textverarbeitung; Tabellenkalkulation; Datenbanken; Bildbearbeitung; Audio/mp3/Video; Security/Viren; E-Mail/Outlook; Internet Browser; SW-Sonstige
Hardware: Mainboard/CPU/RAM; Grafikkarten; Peripherie; HW Sonstiges
Netzwerk: DSL; W-Lan; Telekommunikation; NW-Sonstiges
Programmierung: Skripte(PHP,ASP,Perl...); Webseiten/HTML
Sonstiges: PC-Sonstiges; Plauderecke

Frage

Hi, habe ein kleines Problem. Ich möchte gerne eine Liste mit IP-Adressen sortieren. Leider sortiert es mir Excel folgendermaßen: 12.16.29.1 12.16.29.10 12.16.29.100 12.16.29.12 12.16.29.125 12.16.29.13 und brauchen tue ich es als 12.16.29.1 12.16.29.10 12.16.29.12 12.16.29.13 12.16.29.100 etc. Wie kann ich die Zellen formatieren, damit mir Excel die Zeilen richtig sortiert? Gruß Service9500

Antwort 1 von Saarbauer

Hallo,

Excel liest diese Angaben als Text.

Die Werte sind entsprechend zu ergänzen
z.B.
12.16.29.1 in 120.016.290.001
12.16.29.10 in 120.016.290.010
12.16.29.100 in 120.016.290.100
usw.

Dann werden die Daten richtig gelesen und sortiert

Gruß

Helmut

P.S. Habe jetzt überall auf 3 Stellen erhöht, man könnte auch nur den Letzten bereich entsprechend abändern

Antwort 2 von service9500

Hallo Helmut,

danke für deine Antwort.

Du meinst also, dass ich den Bereich einfach mit Nullen auffüllen soll? ungefähr so :

12.16.29.1 in 012.016.029.002

Wie kann ich denn die nullen auffüllen? Bin noch nicht so geübt in makros schreiben ;-)

Übung macht den Meister....

Gruß

Antwort 3 von M.O.

Hallo,

du könntest dir auch eine Hilfsspalte einrichten, in der du nur die letzten Ziffern der IP-Nummer ausliest und dann nach der Hilfsspalte sortieren.
Wenn du z.B. in A1 deine IP-Adresse steht, könntest du in B1 die folgende Formel schreiben:
=WERT(RECHTS(A1;LÄNGE(A1)-9))
Die Formel kannst du dann einfach nach unten ziehen und dann alles nach Spalte B sortieren.

Gruß

M.O.

Antwort 4 von Saarbauer

Hallo,

die Lösung von @M.O, ist ok., bei meiner Lösung wird
in B1 eingegeben

="0"&TEIL(A1;1;3)&"0"&TEIL(A1;4;3)&"0"&TEIL(A1;7;3)&WENN(LÄNGE(TEIL(A1;10;3))=3;TEIL(A1;10;3);WENN(LÄNGE(TEIL(A1;10;3))=2;"0"&TEIL(A1;10;3);"00"&TEIL(A1;10;3)))

Ergebnis 012.016.029.001 usw.

Anschliessend Daten Makieren und mit "Bearbeiten" "Inhalt Einfügen" "Werte" in Spalte a übertragen

Gruß

Helmut

Antwort 5 von service9500

Super, Danke für eure rasche Hilfe. Ich werde es gleich mal ausprobieren und gebe dann Bescheid...

Gruß

Service9500

Antwort 6 von service9500

Sodele,

also @M.O danke für deine Hilfestellung, aber leider komm ich mit dieser Lösung nicht weiter, da ich ja alle Adressbereiche sortieren muss und brauche daher auch die ersten Stellen.

@Helmut, deine Lösung ist genial. Wenn du mir jetzt noch sagen kannst, wie ich die IP-Adressen nach der Sortierung wieder in den Urzustand bekomme hast du mich MEGA Glücklich gemacht ;-).

Dank nochmals an Beide für die Hilfestellungen!

Gruß aus Stuttgart

Service9500

Antwort 7 von Saarbauer

Hallo,

ich hoffe du hast die Veränderung mit einer Kopie getestet.

Dann führe die Änderung wie von mit vorgeschlagen durch, jedoch lass die Daten in Spalte B stehen.

Bereich markieren

Gehe auf "Daten" "Sortieren" "Sortieren nach" "Spalte B"

Gruß

Helmut

Antwort 8 von service9500

Super, Danke für die Hilfe, es hat jetzt alles soweit geklappt.

Gruß

Sven

Antwort 9 von Cherry

Hallo Helmut

Bei mir funktioniert's nicht. Deine Formel ergibt bei der Adresse 172.168.1.2 = 01720.1608.10.2

Gruss
Michelle

Antwort 10 von nighty

hi all ^^

hier noch ein wenig spielerei bzw eine benuzerdefinierte variante :-))

gruss nighty

Function SumZahlen(Zellen As Variant) As String
    Application.Volatile
    Dim Zelle As Range
    Dim Zeichen As Integer
    Dim Schalter As Boolean
    Dim ArrInd As Integer
    Dim AddSt As Integer
    ReDim Arrfeld(Len([Zellen])) As String
    ArrInd = 1
    If IndexBlock > Len([Zellen]) Then IndexBlock = Len([Zellen])
    For Zeichen = 1 To Len([Zellen])
        If Mid([Zellen], Zeichen, 1) Like "[0-9]" = True Then
            Arrfeld(ArrInd) = Arrfeld(ArrInd) & Mid([Zellen], Zeichen, 1)
            Schalter = True
        End If
        If Schalter = True And Mid([Zellen], Zeichen, 1) Like "[0-9]" = False Then
            ArrInd = ArrInd + 1
            Schalter = False
        End If
    Next Zeichen
    For AddSt = 1 To UBound(Arrfeld)
        If Len(Arrfeld(AddSt)) = 1 Then SumZahlen = SumZahlen & "00" & Arrfeld(AddSt) & "."
        If Len(Arrfeld(AddSt)) = 2 Then SumZahlen = SumZahlen & "0" & Arrfeld(AddSt) & "."
        If Len(Arrfeld(AddSt)) = 3 Then SumZahlen = SumZahlen & Arrfeld(AddSt) & "."
    Next AddSt
    SumZahlen = Mid(SumZahlen, 1, Len(SumZahlen) - 1)
End Function

Antwort 11 von Saarbauer

Hallo @Cherry ,

dein Problem ist mit meiner Lösung

Zitat:
="0"&TEIL(A1;1;3)&"0"&TEIL(A1;4;3)&"0"&TEIL(A1;7;3)&WENN(LÄNGE(TEIL(A1;10;3))=3;TEIL(A1;10;3);WENN(LÄNGE(TEIL(A1;10;3))=2;"0"&TEIL(A1;10;3);"00"&TEIL(A1;10;3)))

nicht zu machen, da dort von anderen Voraussetzungen ausgegangen wird. es handelt sich bei den den IP-Nummern von @service9500 immer um zweistellige Blöcke, bis auf den letzten. bei dir sind 2 dreistellige Blöcke und 2 einstellige. Zudem hast du nur einen Wert zur Verfügung gestellt und wie die anderen aussehen?

Gruß

Helmut

P.S. du solltest in Zukunft einen eigenen Thread eröffnen, da dann besser auf dein spezielles Problem eingegangen werden kann

Antwort 12 von nighty

hi all ^^

hier ein beispiel fuer spalte a

gruss nighty

was los helmut keine ideen ?

was los admin ,die folgefrage ist aktuell und gaeste ausgeschlossen ?

Public Sub Beispiel()
    Dim ZeilenA As Long
    ZeilenA = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
    ReDim intArray(ZeilenA, 1) As Variant
    intArray() = Range("A1:A" & ZeilenA)
    For Index = 1 To ZeilenA
        intArray(Index, 1) = IPer(intArray(Index, 1))
    Next Index
    Range("A1:A" & ZeilenA) = intArray()
End Sub

Function IPer(Zellen As Variant) As String
    Application.Volatile
    Dim Zelle As Range
    Dim Zeichen As Integer
    Dim Schalter As Boolean
    Dim ArrInd As Integer
    Dim AddSt As Integer
    ReDim Arrfeld(Len([Zellen])) As String
    ArrInd = 1
    If IndexBlock > Len([Zellen]) Then IndexBlock = Len([Zellen])
    For Zeichen = 1 To Len([Zellen])
        If Mid([Zellen], Zeichen, 1) Like "[0-9]" = True Then
            Arrfeld(ArrInd) = Arrfeld(ArrInd) & Mid([Zellen], Zeichen, 1)
            Schalter = True
        End If
        If Schalter = True And Mid([Zellen], Zeichen, 1) Like "[0-9]" = False Then
            ArrInd = ArrInd + 1
            Schalter = False
        End If
    Next Zeichen
    For AddSt = 1 To UBound(Arrfeld)
        If Len(Arrfeld(AddSt)) = 1 Then IPer = IPer & "00" & Arrfeld(AddSt) & "."
        If Len(Arrfeld(AddSt)) = 2 Then IPer = IPer & "0" & Arrfeld(AddSt) & "."
        If Len(Arrfeld(AddSt)) = 3 Then IPer = IPer & Arrfeld(AddSt) & "."
    Next AddSt
    IPer = Mid(IPer, 1, Len(IPer) - 1)
End Function

Antwort 13 von Saarbauer

Hallo @nighty,

Ideen schon, blos gestern keine Lust (Weiberfastnacht).

Ausserdem ist eine Angabe etwas Mager man sollte schon in etwa wissen wie die anderen Daten aufgebaut sind (gleiches Schema oder anders).

Gruß

Helmut

Antwort 14 von nighty

hi all ^^

@helmut
:-)

@all
noch zum abschluss

die folgende syntax ist nur notwendig zur aktualisierung bei direkten einsatz einer function,im obigen beispiel also nicht notwendig da der aufruf ja ueber ein makro erfolgt

Application.Volatile

gruss nighty

Antwort 15 von Cherry

Hallo zusammen

Sorry für die wenigen Angaben. Ich hatte eine Lösung für alle Adressen gesucht, es sollte egal welche Oktetts wieviele Zahlen hat. Die Lösung von Helmut ist wie ich herausgefunden hab zu statisch.

Danke Nighty für dein Code, werde es mal ausprobieren.

Habe übrigens hier angehängt, weil ich genau das gleiche Problem hatte wie serice9500, nur für x-beliebige IP-Adresse. Ich muss es sortieren können. Wenn ich ein neues Tread eröffnet hätte, wäre das auch nicht recht gewesen!

Nochmals vielen Dank für eure Hilfe.

Gruss
Michelle

Antwort 16 von Saarbauer

Hallo,

nur mit einer Formel ist es sehr schwierig zu machen, da du die Trennungspunkte jedesmal suchenmusst und danndarüber die Auffüllnullen ermitteln. Ich bin an der maximalen längeder Formel gescheitert, daher ist es aus meiner Sicht hier nur mit einem Makro zu machen.

Da @nighty schon einen Vorschlag gemacht hat, der aus meiner Sicht funktionieren müsse, mach ich keinen weiteren.

Gruß

Helmut