Extract single IP address from a string

[IanPC]

Hello - I have a spreadsheet of URLs, some of which contain IP addresses and some don't. I'm struggling to write an excel formula that will identify the URLs with IP addresses in them, and extract that IP address into another cell. What formula would I paste down column B to achieve this?


[TABLE="width: 500"]
<tbody>[TR]
[TD]URL (column A)[/TD]
[TD]Desired output (column B)[/TD]
[/TR]
[TR]
[TD]http://sample.domain/the/eed/dhgg.html[/TD]
[TD][#Error of some kind][/TD]
[/TR]
[TR]
[TD]http://10.22.88.11/eid/tedld/ed.html[/TD]
[TD]10.22.88.11[/TD]
[/TR]
[TR]
[TD]http://242.25.23.7/ede/vkjd/tt.html[/TD]
[TD]242.25.23.7[/TD]
[/TR]
[TR]
[TD]etc[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help gratefully received. Many thanks.

 

[IanPC]

Yes, in my case the IP addresses will all start with //.

 

[Rick Rothstein]

Yes, in my case the IP addresses will all start with //.

In that case, here is another UDF (user defined function) that you can consider...

Function IP(s As String) As String
  Dim X As Long, V As Variant
  IP = Split(s & "//", "/")(2)
  If IP Like "*.*.*.*" And Not IP Like "*[!0-9.]*" Then
    For Each V In Split(IP, ".")
      If V > 255 Then
        IP = "No IP"
        Exit Function
      End If
    Next
  Else
    IP = "No IP"
  End If
End Function

 

[Peter_SSs]

In that case, here is another UDF (user defined function) that you can consider...

Pretty likely not possible with the OP's data, but that function returns an invalid IP address for a string like "http://123.0089.34.9/abc" whereas my earlier udf correctly dismisses it, as does this standard worksheet formula - if you don't mind the size of it!

=IFERROR(IF(AND(
MAX(ABS(52.5-CODE(MID(SUBSTITUTE(MID(A2,FIND("//",A2)+2,FIND("/",A2,9)-FIND("//",A2)-2),".","")&REPT(0,15),ROW(INDIRECT("1:15")),1))))<5,
LEN(MID(A2,FIND("//",A2)+2,FIND("/",A2,9)-FIND("//",A2)-2))-LEN(SUBSTITUTE(MID(A2,FIND("//",A2)+2,FIND("/",A2,9)-FIND("//",A2)-2),".",""))=3,
MAX(LEN(TRIM(MID(SUBSTITUTE(MID(A2,FIND("//",A2)+2,FIND("/",A2,9)-FIND("//",A2)-2),".",REPT(" ",100)),{1,100,200,300},100))))<4,
MAX(MID(SUBSTITUTE(MID(A2,FIND("//",A2)+2,FIND("/",A2,9)-FIND("//",A2)-2),".",REPT(" ",100)),{1,100,200,300},100)+0)<256),
MID(A2,FIND("//",A2)+2,FIND("/",A2,9)-FIND("//",A2)-2),"No IP"),"No IP")

 

[Rick Rothstein]

Pretty likely not possible with the OP's data, but that function returns an invalid IP address for a string like "http://123.0089.34.9/abc" whereas my earlier udf correctly dismisses it, as does this standard worksheet formula - if you don't mind the size of it!

While software specific, leading zeros are allowed to make each number 3 digits long. Some software treats numbers like 036 the same as 36, but others would treat that as an Octal number which would make it 30 instead of 36). Given that leading zeros can be evaluated by some systems as being the same as the number without the leading zeros, I would be willing to bet extra leading zeros would be ignored in the same way (not sure how a system that treats 036 as an Octal value would react). Anyway, to address numbers that end up being 4 digits long where the extra digits are zero in the way you have requires only a minor modification to my code...

Function IP(s As String) As String
  Dim X As Long, V As Variant
  IP = Split(s & "//", "/")(2)
  If IP Like "*.*.*.*" And Not IP Like "*[!0-9.]*" Then
    For Each V In Split(IP, ".")
      If V > 255 Or Len(V) > 3 Then
        IP = "No IP"
        Exit Function
      End If
    Next
  Else
    IP = "No IP"
  End If
End Function

By the way, I would note that an IP address can also be valid with a slashed number at the end which my code does not allow for.[/QUOTE]

 

[Ingolf]

@Rick,

Your cod:

Function IP(s As String) As String
  Dim X As Long, V As Variant
  IP = Split(s & "//", "/")(2)
  If IP Like "*.*.*.*" And Not IP Like "*[!0-9.]*" Then
    For Each V In Split(IP, ".")
      If V > 255 Or Len(V) > 3 Then
        IP = "No IP"
        Exit Function
      End If
    Next
  Else
    IP = "No IP"
  End If
End Function

is not working for http://242.25.423.7/ede/vkjd/tt.html result is No IP

 

[Rick Rothstein]

@Rick,

Your cod:

is not working for http:// 242.25.423.7/ede/vkjd/tt.html result is No IP

423 is not an allowed value for a real IP address (the number must be in the range 0 to 255).

 

[Faker4442]

I have a similar issue, but my original format is not in HTTP format. The formats are random and I am looking to extract the IP address, if there is one. How can I extract the IP address if the original text is random?

Imagine Encoder 107.8.2.116

Encoder 107.8.3.185

170.8.2.116 Encoder

Imagine 24.174.45.215

 

[sandy666]

try

source	IP
Imagine Encoder 107.8.2.116	107.8.2.116
Encoder 107.8.3.185	107.8.3.185
170.8.2.116 Encoder	170.8.2.116
Imagine 24.174.45.215	24.174.45.215

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IP = Table.AddColumn(Source, "IP", each Text.Trim(Text.Select([source],{"0".."9","."}),"."))
in
    IP

 

[Faker4442]

try

source	IP
Imagine Encoder 107.8.2.116	107.8.2.116
Encoder 107.8.3.185	107.8.3.185
170.8.2.116 Encoder	170.8.2.116
Imagine 24.174.45.215	24.174.45.215

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    IP = Table.AddColumn(Source, "IP", each Text.Trim(Text.Select([source],{"0".."9","."}),"."))
in
    IP

Sandy,

Thanks for the assistance, however, I am not sure exactly how to implement this solution as when I try to build it in VBA, it doesn't seem to work. Is this code just a part of one of the overall larger solutions earlier in this thread or am I missing something here? Sorry if this seems like a stupid question, but I am still very new to most of this.

 

[sandy666]

check the name of CODE (Power Query not a vba!!!)
you've Power Query aka Get&Transform build-in to XL2016