Extract single IP address from a string

IanPC

New Member
Joined
Jul 3, 2018
Messages
3
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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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:
Code:
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?
 
Upvote 0
Here's an example of the VBA function:

Code:
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
 
Last edited:
Upvote 0
Looking at it, my code is practically the same as Ron's formula.

The formula is faster also.
 
Upvote 0
Hi,

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


Book1
AB
1http://sample.domain/the/eed/dhgg.htmlNo IP
2http://10.22.88.11/eid/tedld/ed.html10.22.88.11
3https://242.25.23.7/ede/vkjd/tt.html242.25.23.7
4http://101.222.888.111/eid/tedld/ed.html101.222.888.111
5https://242.255.233.777/ede/vkjd/tt.html242.255.233.777
6https://www.mrexcel.com/forum/excel-questions/1061726-extract-single-ip-address-string.html#post5098899No IP
Sheet117
Cell Formulas
RangeFormula
B1=IFERROR(MID(A1,SEARCH("//*.*.*.*/",A1)+2,FIND("/",A1,SEARCH("//*.*.*.*/",A1)+2)-SEARCH("//*.*.*.*/",A1)-2),"No IP")


Formula copied down.
 
Last edited:
Upvote 0
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.
Code:
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
ABCDE
1DataPost 6Post 2Post 3Post 5
2http://www.mrexcel.com.au/other/No IPNo IP[#Error of some kind]www.mrexcel.com.au
3http://1intra.tm.com.my.dnstree.com/No IPNo IP1intra.tm.com.my.dnstree.com1intra.tm.com.my.dnstree.com
4http://1.1.E.1/abcNo IP1.1.E.11.1.E.11.1.E.1
5http://10.22.88.11/eid/tedld/ed.html10.22.88.1110.22.88.1110.22.88.1110.22.88.11
6http://242.25.23.7/ede/vkjd/tt.html242.25.23.7242.25.23.7242.25.23.7242.25.23.7
7http://242.25.423.7/ede/vkjd/tt.htmlNo IP242.25.423.7242.25.423.7242.25.423.7
IP
 
Upvote 0
@Peter_SSs, since you already have it set up, may be include a https sample?
 
Last edited:
Upvote 0
@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
ABCDEF
1DataPost 6Post 8Post 2Post 3Post 5
2http://www.mrexcel.com.au/otherNo IPNo IPNo IP[#Error of some kind]www.mrexcel.com.au
3http://1intra.tm.com.my.dnstree.com/No IPNo IPNo IP1intra.tm.com.my.dnstree.com1intra.tm.com.my.dnstree.com
4http://1.1.E.1/abcNo IP1.1.E.11.1.E.11.1.E.11.1.E.1
5http://10.22.88.11/eid/tedld/ed.html10.22.88.1110.22.88.1110.22.88.1110.22.88.1110.22.88.11
6http://242.25.23.7/ede/vkjd/tt.html242.25.23.7242.25.23.7242.25.23.7242.25.23.7242.25.23.7
7http://242.25.423.7/ede/vkjd/tt.htmlNo IP242.25.423.7242.25.423.7242.25.423.7242.25.423.7
8https://www.mrexcel.com/forum/excel-questions/..No IPNo IPNo IP#VALUE!No IP
9https://242.25.23.7/ede/vkjd/tt.html242.25.23.7242.25.23.7No IP#VALUE!242.25.23.7
10https://242.255.233.777/ede/vkjd/tt.htmlNo IP242.255.233.777No IP#VALUE!242.255.233.777
11http://abc/de.f/gh/i.j/k.l/mNo IPNo IPNo IP[#Error of some kind]abc
IP
 
Last edited:
Upvote 0
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)?
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top