Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Oktetts einer IP-Adresse (String) extrahieren

Oktetts einer IP-Adresse (String) extrahieren
01.10.2006 00:19:48
Frank
Hallo,
folgendes Problem:
In einer Zelle steht eine IP-Adresse als String: 172.16.30.35
Wie kann ich die einzelnen, durch Punkte getrennte Oktette daraus
extrahieren und jeweils in einer anderen Zelle darstellen?
Vielen Dank!
Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Oktetts einer IP-Adresse (String) extrahieren
01.10.2006 00:39:47
Josef
Hallo Frank!
Entweder per &gt "Daten" &gt "Text in Spalten", oder per Formel.
Tabelle1

 ABCDE
1172.16.30.35172163035
2100.1.1.1100111
3255.0.0.0255000
4145.93.78.14145937814

Formeln der Tabelle
ZelleFormel
B1=LINKS(A1;FINDEN(".";A1)-1)
C1=LINKS(WECHSELN($A1;B1&".";"");FINDEN(".";WECHSELN($A1;B1&".";""))-1)
D1=LINKS(WECHSELN($A1;B1&"."&C1&".";"");FINDEN(".";WECHSELN($A1;B1&"."&C1&".";""))-1)
E1=WECHSELN($A1;B1&"."&C1&"."&D1&".";"")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Sepp
Anzeige
AW: Oktetts einer IP-Adresse (String) extrahieren
01.10.2006 01:03:39
FP
hallo Franz,
1) markiere die Spalte in der die IPs stehen
2) wähle Menü "Daten - Text in Spalten"
3) Option "getrennt"
4) Klick auf "weiter"
5) Häkchen bei "Andere" und im Feld dahinter .
6) Klick auf "Fertig stellen"
@Sepp: was macht wohl Deine Formel bei dieser IP-Adresse ? ;-)
255.255.255.0
Servus aus dem Salzkammergut
Franz
Anzeige
AW: Oktetts einer IP-Adresse (String) extrahieren
01.10.2006 09:23:53
Josef
Hallo Franz!
Dann hängt man halt bei WECHSELN() noch den dritten Parameter dran;-))
Tabelle1

 ABCDE
1255.0.0.0255000
2255.255.255.02552552550
3255.255.255.255255255255255
40.0.0.00000
50.255.255.2550255255255
6255.0.255.025502550
70.0.0.255000255

Formeln der Tabelle
ZelleFormel
B1=LINKS(A1;FINDEN(".";A1)-1)
C1=LINKS(WECHSELN($A1;B1&".";"";1);FINDEN(".";WECHSELN($A1;B1&".";"";1))-1)
D1=LINKS(WECHSELN($A1;B1&"."&C1&".";"";1);FINDEN(".";WECHSELN($A1;B1&"."&C1&".";"";1))-1)
E1=WECHSELN($A1;B1&"."&C1&"."&D1&".";"";1)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Sepp
Anzeige
ich weiss ;-)
01.10.2006 21:05:36
FP
Hallo Sepp,
aber ob das der Franz auch gewußt hat ?
Aber jetzt bin ich zufrieden :-)
Wünsche Dir einen gemütlichen Rest-Sonntag und
Servus aus dem Salzkammergut
Franz
AW: Oktetts einer IP-Adresse (String) extrahieren
ransi
HAllo
Geht auch mit einer einfachen benutzerdefinierten Funktion:


Public Function splitten(zelle, Optional Welche_Stelle As Integer = 1, Optional Trenner As String = " ")
Dim a As Variant
a = Split(zelle, Trenner)
splitten = a(Welche_Stelle - 1)
End Function


Tabelle1

 ABCDE
1172.16.30.35172163035

Formeln der Tabelle
ZelleFormel
B1=splitten($A$1;SPALTE(A1);".")
C1=splitten($A$1;SPALTE(B1);".")
D1=splitten($A$1;SPALTE(C1);".")
E1=splitten($A$1;SPALTE(D1);".")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
ransi
Anzeige
AW: Oktetts einer IP-Adresse (String) extrahieren
ransi
Hallo
Hier noch was ganz extravagantes:
Tabelle1

 ABCDEFG
1123.456.789.09.ab12345678909ab 
2       

Formeln der Tabelle
ZelleFormel
B1=WENN(ANZAHL2(feld) < SPALTE(A1);"";INDEX(feld;SPALTE(A1)))
Namen in Formeln
ZelleNameBezieht sich auf
B1feld=AUSWERTEN("{"&""""&WECHSELN(Tabelle1!$A1;".";""""&"."&"""")&""""&"}")


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Hier gefunden:
http://www.excelformeln.de/formeln.html?welcher=210
ransi
Anzeige
AW: Oktetts einer IP-Adresse (String) extrahieren
01.10.2006 16:41:48
Frank
Super, das hat mir weitergeholfen!
Vielen Dank an alle!
;
Anzeige
Anzeige

Infobox / Tutorial

Oktetts einer IP-Adresse in Excel extrahieren


Schritt-für-Schritt-Anleitung

Um die Oktetts einer IP-Adresse aus einem String in Excel zu extrahieren, kannst du folgende Schritte befolgen:

  1. IP-Adresse eingeben: Schreib die IP-Adresse (z.B. 172.16.30.35) in Zelle A1.
  2. Text in Spalten:
    • Markiere die Zelle A1 oder die gesamte Spalte, die die IP-Adressen enthält.
    • Gehe zu Daten > Text in Spalten.
    • Wähle die Option Getrennt und klicke auf Weiter.
    • Setze ein Häkchen bei Andere und gib . (Punkt) in das Feld ein.
    • Klicke auf Fertig stellen.
  3. Formel verwenden: Wenn du die Oktetts mit einer Formel extrahieren möchtest, verwende die folgenden Formeln für die Zellen B1 bis E1:
    • B1: =LINKS(A1;FINDEN("." ;A1)-1)
    • C1: =LINKS(WECHSELN($A1;B1 & "."; ""); FINDEN("." ;WECHSELN($A1;B1 & "."; ""))-1)
    • D1: =LINKS(WECHSELN($A1;B1 & "." & C1 & "."; ""); FINDEN("." ;WECHSELN($A1;B1 & "." & C1 & "."; ""))-1)
    • E1: =WECHSELN($A1;B1 & "." & C1 & "." & D1 & "."; "")

Häufige Fehler und Lösungen

  • Fehler 1: Die Formel gibt einen Fehler aus.

    • Lösung: Stelle sicher, dass die IP-Adresse im richtigen Format eingegeben wurde. Punktierte IP-Adressen sind notwendig.
  • Fehler 2: Text in Spalten funktioniert nicht.

    • Lösung: Überprüfe, ob du das richtige Trennzeichen (.) ausgewählt hast.

Alternative Methoden

Eine einfache Alternative zur Verwendung von Formeln ist die Erstellung einer benutzerdefinierten Funktion. Hier ist ein Beispiel:

Public Function splitten(zelle As String, Optional Welche_Stelle As Integer = 1, Optional Trenner As String = ".")
    Dim a As Variant
    a = Split(zelle, Trenner)
    splitten = a(Welche_Stelle - 1)
End Function

Benutze dann die Funktion in den Zellen B1 bis E1 wie folgt:

  • B1: =splitten($A$1;SPALTE(A1);".")
  • C1: =splitten($A$1;SPALTE(B1);".")
  • D1: =splitten($A$1;SPALTE(C1);".")
  • E1: =splitten($A$1;SPALTE(D1);".")

Praktische Beispiele

Hier sind einige Beispiele für IP-Adressen und deren Zerlegung:

IP-Adresse Oktett 1 Oktett 2 Oktett 3 Oktett 4
172.16.30.35 172 16 30 35
255.255.255.0 255 255 255 0
100.1.1.1 100 1 1 1

Diese Beispiele zeigen, wie die Formeln die IP-Adresse zerlegen.


Tipps für Profis

  • Wenn du mit einer großen Liste von IP-Adressen arbeitest, kannst du die Formeln einfach nach unten ziehen, um die Werte für alle Zeilen zu extrahieren.
  • Du kannst auch die Formatierung der IP-Adressen anpassen, um sie besser lesbar zu machen. Beispielsweise kannst du die Zellen in einem bestimmten Farbschema hervorheben oder die Schriftart ändern.

FAQ: Häufige Fragen

1. Wie kann ich die IP-Adressen in Excel sortieren?
Du kannst die Spalten mit den extrahierten Oktetts markieren und die Sortierfunktion in Excel verwenden, um die Adressen nach bestimmten Kriterien zu sortieren.

2. Was ist der Unterschied zwischen IPv4 und IPv6?
IPv4-Adressen bestehen aus 4 Oktetten, während IPv6-Adressen aus 8 Gruppen bestehen. Die Methoden zur Zerlegung in Excel sind nur für IPv4 anwendbar.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige