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.

 

[Ron Coderre]

Ideally, you'd use VBA to write a custom function that can more accurately match IP Address patterns.

However, with your list in Col_A, I think this formula might be adequate for what you want:

B1: =IF(ISNUMBER(--SUBSTITUTE(MID(LEFT(A1,FIND("/",A1,8)-1),8,LEN(A1)),".","")),MID(LEFT(A1,FIND("/",A1,8)-1),8,LEN(A1)),"No IP")

Is that something you can work with?

 

[Georgiboy]

Here's an example of the VBA function:

Function GetIP(rng As Range)    
    Dim str As String, ipLen As Long
    Dim frmC As Long, toC As Long
    Dim tmpNum As Integer
    
    str = rng.Value
    frmC = Application.Search("http://", str) + 7
    toC = Application.Search("/", str, frmC)
    ipLen = toC - frmC
    On Error GoTo ErrHnd
    tmpNum = Mid(str, frmC, 1)
    On Error GoTo 0
    GetIP = Mid(str, frmC, ipLen)
    Exit Function
    
ErrHnd:
    GetIP = "[#Error of some kind]"
    Exit Function

End Function

Hope this helps

 

[Georgiboy]

Looking at it, my code is practically the same as Ron's formula.

The formula is faster also.

 

[jtakw]

Hi,

In case you have Regular and Secured HTTP (https) like this site (MrExcel):

Book1
	A	B
1	http://sample.domain/the/eed/dhgg.html	No IP
2	http://10.22.88.11/eid/tedld/ed.html	10.22.88.11
3	https://242.25.23.7/ede/vkjd/tt.html	242.25.23.7
4	http://101.222.888.111/eid/tedld/ed.html	101.222.888.111
5	https://242.255.233.777/ede/vkjd/tt.html	242.255.233.777
6	https://www.mrexcel.com/forum/excel-questions/1061726-extract-single-ip-address-string.html#post5098899	No IP
Sheet117

Cell Formulas
Range	Formula
B1	=IFERROR(MID(A1,SEARCH("//*.*.*.*/",A1)+2,FIND("/",A1,SEARCH("//*.*.*.*/",A1)+2)-SEARCH("//*.*.*.*/",A1)-2),"No IP")

Formula copied down.

 

[Peter_SSs]

Most likely Ron's formula will be sufficient for the OP, and it is more robust than the other suggestions so far anyway - see sample data and results below.
If, a more robust checking of IP addresses is required, here is another UDF to consider. I am not an IP address expert so I don't know if this would exclude all invalid IP address.

Function IP(s As String) As String
  Static RX As Object
  Dim M As Object
  Dim itm As Variant
  
  If RX Is Nothing Then Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "\/{2}((\d{1,3}\.){3}\d{1,3})\/"
  Set M = RX.Execute(s & "//300.0.0.0/")
    IP = Replace(M.Item(0).Submatches(0), "/", "")
    For Each itm In Split(IP, ".")
      If itm > 255 Then
        IP = "No IP"
        Exit Function
      End If
    Next itm
End Function

Excel Workbook
	A	B	C	D	E
1	Data	Post 6	Post 2	Post 3	Post 5
2	http://www.mrexcel.com.au/other/	No IP	No IP	[#Error of some kind]	www.mrexcel.com.au
3	http://1intra.tm.com.my.dnstree.com/	No IP	No IP	1intra.tm.com.my.dnstree.com	1intra.tm.com.my.dnstree.com
4	http://1.1.E.1/abc	No IP	1.1.E.1	1.1.E.1	1.1.E.1
5	http://10.22.88.11/eid/tedld/ed.html	10.22.88.11	10.22.88.11	10.22.88.11	10.22.88.11
6	http://242.25.23.7/ede/vkjd/tt.html	242.25.23.7	242.25.23.7	242.25.23.7	242.25.23.7
7	http://242.25.423.7/ede/vkjd/tt.html	No IP	242.25.423.7	242.25.423.7	242.25.423.7
IP

 

[jtakw]

@Peter_SSs, since you already have it set up, may be include a https sample?

 

[Peter_SSs]

@Peter_SSs, since you already have it set up, may be include a https sample?

I originally interpreted your comment as relating to my UDF, but I assume now that it was actually in relation to my comments about the robustness of Ron's formula and you are right IF the OP has data of the form you are suggesting - but not shown in their samples. However, an adaption that still checks reasonably well (except examples like row 4 - if such examples are possible) that the extracted data is numeric, is given in column C below.

Excel Workbook
	A	B	C	D	E	F
1	Data	Post 6	Post 8	Post 2	Post 3	Post 5
2	http://www.mrexcel.com.au/other	No IP	No IP	No IP	[#Error of some kind]	www.mrexcel.com.au
3	http://1intra.tm.com.my.dnstree.com/	No IP	No IP	No IP	1intra.tm.com.my.dnstree.com	1intra.tm.com.my.dnstree.com
4	http://1.1.E.1/abc	No IP	1.1.E.1	1.1.E.1	1.1.E.1	1.1.E.1
5	http://10.22.88.11/eid/tedld/ed.html	10.22.88.11	10.22.88.11	10.22.88.11	10.22.88.11	10.22.88.11
6	http://242.25.23.7/ede/vkjd/tt.html	242.25.23.7	242.25.23.7	242.25.23.7	242.25.23.7	242.25.23.7
7	http://242.25.423.7/ede/vkjd/tt.html	No IP	242.25.423.7	242.25.423.7	242.25.423.7	242.25.423.7
8	https://www.mrexcel.com/forum/excel-questions/..	No IP	No IP	No IP	#VALUE!	No IP
9	https://242.25.23.7/ede/vkjd/tt.html	242.25.23.7	242.25.23.7	No IP	#VALUE!	242.25.23.7
10	https://242.255.233.777/ede/vkjd/tt.html	No IP	242.255.233.777	No IP	#VALUE!	242.255.233.777
11	http://abc/de.f/gh/i.j/k.l/m	No IP	No IP	No IP	[#Error of some kind]	abc
IP

 

[Rick Rothstein]

Hello - I have a spreadsheet of URLs, some of which contain IP addresses and some don't.

The ones that have IP addresses, will those IP addresses always come immediately after the double slashes as your examples show (or did you simply generalize your problem by posting non-representative examples of your values)?

 

[IanPC]

Thanks - that's a big help.