Extract IP from TEXT in EXCEL

Hallo zusammen,
ich suche eine Excel-Formel (keine VBA Lösung), die mir die IP-Adresse aus einem Text in der Zelle extrahiert:

Die IP befindet sich mitten im Text auch teilweise mit Leerzeichen davor und dahinter:

Beilspiel - Text mit IP in A1 Zelle:
ABCD001 - DHCP - 10.133.123.123 - Unitsadffaasffda

Um den vorderen Teil entfernen, habe ich folgende Beispiel Formel:

Beispiel1:
=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A2," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A2))))),LEN(A2)),",",REPT(" ",50)),50)),"")

Beispiel2:
=IFERROR(LEFT(MID(A2,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A2," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A2))))),LEN(A2)),FIND(",",MID(A2,LOOKUP(9^9,FIND("abcd",SUBSTITUTE(SUBSTITUTE(" "&A2," 10."," 11.")," 11.","abcd"),ROW(INDIRECT("1:"&LEN(A2))))),LEN(A2))&",")-1),"")

Beispiel3 als Matrix:
{=IFERROR(TRIM(LEFT(SUBSTITUTE(MID(A2,LOOKUP(9^9,FIND(" 11.",SUBSTITUTE(" "&A2," 10."," 11."),ROW($2:$100))),99),",",REPT(" ",99)),99)),"")}

Vielen Danke und viele Grüße

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 9803708351

Url: https://administrator.de/contentid/9803708351

Ausgedruckt am: 22.11.2024 um 15:11 Uhr

3 Kommentare

Neuester Kommentar

REGEXEXTRACT Function

=REGEXEXTRACT(A1;"\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}")

Den Regex kann man natürlich noch genauer spezifizieren, aber für den Hausgebrauch reicht das in der Regel wenn im Text keine Verwechslungsgefahr besteht.

Gruß WRK

Oder alternativ auch so:
Spalte A sind die Texte, in Zelle B1 nun die erste IP-Adresse von Hand eintragen, dann den Cursor darunter platzieren und STRG+E drücken

, voila werden alle IPs wie von Geisterhand von Excel automatisch extrahiert.

Wenn dein Excel noch zu alt ist, sollte die klassische Variante für deinen Fall reichen

=GLÄTTEN(WECHSELN(TEIL(A1;SUCHEN(".";A1)-3;SUCHEN(" ";A1;SUCHEN(".";A1))-(SUCHEN(".";A1)-3));"-";""))  

Moin,

unser Powershell-Regex-Spezialist, derzeit WolleRoseKaufe, war schneller, spare mir daher meinen Kommentar, ist natürlich perfekt. Eher grundsätzlich: so etwas ist immer ein Fall für eine wie auch immer geartete Regex-Funktion.

Gruß

DivideByZero