Listen mit IP-Adressen in Excel bearbeiten

Bei der Verarbeitung von Listen mit IP-Adressen in Excel kommt es zu dem Umstand, dass manche IP-Adressen als Text, andere als Zahlen gelesen werden. Die IP-Adressen, die als Zahlen von Excel erkannt werden, sind dann leider unbrauchbar.

Quelle: CSV-Datei

"10.10.10.5";"Bruno"
"10.10.10.127";"Klaus"
"10.121.10.127";"Peter"
"10.102.150.127";"Alex"

Liest man diese CSV-Datei in Excel ein oder kopiert den Text in eine Spalte und verwendet anschließend "Daten" -> "Text in Spalten", so bringt Excel folgendes Ergebnis:

Daten - Text in Spalten

Die IP-Adressen in den Zellen A1-A3 sind korrekt übernommen worden, jedoch in A4 wurde statt 10.102.150.127 die Zahl 10102150127 übernommen. Die Anzeige der Zelle ist zwar korrekt, aber der dahinterstehende Wert ist nun kein Text mehr, sondern eine Zahl. Zwei Dinge sind verantwortlich dafür, dass Excel diese IP-Adresse als Zahl erkennt:

  • die hinteren Zahlen sind alle dreistellig und
  • das Tausendertrennzeichen ist der Punkt.

Wie kann jetzt eine IP-Adresse mit dreistelligen Zahlen als Text übernommen werden?

Bei der Umwandlung "Text in Spalten" kann im Schritt 3 des Textkonvertierungsassistenten das Tausendertrennzeichen eingestellt werden.

Textkonvertierungsassistent

-> Button "Weitere"

Tausendertrennzeichen wählen

Hier als Tausendertrennzeichen etwas anderes als den Punkt wählen (z.B. Komma) und mit OK den Dialog schließen. Nach Klick auf den Button "Fertig stellen" hat Excel die IP-Adresse als Zahl übernommen.

Falls dies zu aufwändig erscheint, so hilft folgende Excel-Formel, den Wert als korrekte IP-Adresse anzuzeigen:

=TEXT(A4;"##0.000.000.000")

Allerdings steht dann die IP-Adresse in einer anderen Zelle. Mit Kopieren der Formelzelle und einfügen via "Bearbeiten" -> "Inhalte einfügen" -> "Werte" kann die textformatierte IP-Adresse in die ursprüngliche Zelle kopiert werden.