winget
Jul 16, 2024, updated at 09:06:46 (UTC)
view211
view3
0
Goto Top

Extract IP from TEXT in EXCEL

GermansolvedQuestionMicrosoft Office
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
comment-contentCommentShareShare
Share on Facebook Share on Twitter Share on Reddit Share on Linkedin

Content-Key: 9803708351

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

Printed on: July 16, 2024 at 12:07 o'clock

3 Comments
Latest comment
Goto Top
Member: WolleRoseKaufe
Solution WolleRoseKaufe Jul 16, 2024 updated at 10:09:46 (UTC)
Goto Top
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 face-wink, voila werden alle IPs wie von Geisterhand von Excel automatisch extrahiert.

screenshot

screenshot

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));"-";""))
heart2
Member: DivideByZero
DivideByZero Jul 16, 2024 at 09:22:15 (UTC)
Goto Top
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
Member: winget
winget Jul 16, 2024 at 11:29:12 (UTC)
Goto Top
Super Lösung mit REGEXEXTRACT. Leider habe ich eine ältere Excel-Version und somit mit Glätten funktioniert perfekt.
Vielen Dank und viele Grüße
GermansolvedQuestionMicrosoft Office