VBA: Parsing multiple IP addresses from a string

S

sushinutnc

New Member
Joined
Jul 23, 2009
Messages
15
I have some code that will parse the FIRST IP address it finds in a string. Could someone help with the code for continuing through the remaining part of the string and parsing out any additional IP addresses it finds and concatenating them with a comma and space (", ")?

There are specific rules for validating a "proper" IP, but for the purposes of my work, I don't need to validate. I found the following code on a google group (thanks Jeff M). All it does is parse the numeric text surrounding the 3 periods that all valid IPv4 addresses contain. For my purposes, it works.

*******************************
Function FindIP(IP As String) As String
Dim cnt As Integer, NoOfDots As Integer
Dim i As Integer, c As Integer

For i = 1 To Len(IP)
If IsNumeric(Mid(IP, i, 1)) Or Mid(IP, i, 1) = "." Then
cnt = cnt + 1
Else
'More than 6 consecutive char's that are
'either numeric or dot (".")
If cnt > 6 Then
FindIP = Mid(IP, i - cnt, cnt)

'Count the number of dot's
NoOfDots = 0
For c = 1 To Len(FindIP)
If Mid(FindIP, c, 1) = "." Then
NoOfDots = NoOfDots + 1
End If
Next c

'Check no of dots (s/b 3)
If NoOfDots = 3 Then
Exit Function
Else
FindIP = ""
End If

End If
cnt = 0
End If
Next i
End Function
*******************************

So, what I need is for this code to loop through the rest of the input string and pull out any additional IPs (an array?).

Here are two examples (there may be 100s of chars in a input field. These are just examples).

************

EXAMPLE INPUT1:
We have a problem with 192.168.3.43 and also with 172.16.234.12. Also might have a problem with 10.168.4.1.

OUTPUT with above code is:
192.168.3.43

DESIRED OUTPUT:
192.168.3.43, 172.16.234.12, 10.168.4.1
************

************
EXAMPLE INPUT2:
There is a system at 192.168.23.142, but it's not communicating with 192.168.4.132. Please call me at 555.432.2534 to see if it's talking to 10.4.22.3. My IP is 172.16.4.12. Thanks!

OUTPUT with above code is:
192.168.23.142

DESIRED OUTPUT:
192.168.23.142, 192.168.4.132, 10.4.22.3, 172.16.4.12
************


(BTW, I didn't want to get technical with this, but if anyone wants to validate parsed IPs, they are comprised of four, 8-bit numbers (octets) separated by three "dots". For the purposes of this discussion valid IPs could range from "0.0.0.0" to "255.255.255.255"; i.e., each octet can range from 0-255).

THANK you in advance for any help!
 
Sort by date Sort by votes
Yes, 10.10.10.10 and 010.010.010.010 are equivalent, but should be considered the same (ideally). Output should be unpadded.
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Hi, PGC... This has turned into more of a project than I needed... I have a clean set of output now, but wanted to make sure I provided feedback in case you or others are still interested in further work on the code. Again, I appreciate your help and am not expecting you to address these. ;)

I revalidated your latest Function against my original dataset, as well as the output from your last Function prior. It resulted in only four "errors" (out of hundreds of thousands characters of parsed text, that's pretty impressive):

InputString1: CM-V6.5.4.3 VDESVT
Output1: 6.5.4.3
Desired: Should be null (for my purposes). I guess you could say an IP shouldn't have an alpha in front of the first octet, but for some other people, this might not be a golden rule.

InputString2: Toolbar for Internet Explorer [4.0.0.002]
Output2: 4.0.0.002
Desired: Should be null. Can't get around this... I might have legitimate IPs in brackets, parentheses, etc... so really can't get around this, unless you use two functions-- one for "standard" octets and one including zero-padded octets (not common when working with data from IT systems as inputs, but more common when working with IPs in Excel, since it's commonly used for sorting).

InputString3: Twenty Five Million USDollars [ 25.000.000.00 USD]
Output3: 25.000.000.00
Desired: Should be null. The only "rule" I can see this breaking is that an octet should never be "00"... it should be "0" or "000" (if padded). An interesting error, but certainly not worth fixing the code. The two zeroes are cents and the whole expression is a legitimate international currency string using decimals for the thousands separator, so... not worth coding, IMO.

And now, the more interesting one--

InputString4: blahblahblah.com/search?q=%22192.168.10.20%22
Output4: (null)
Desired: Should be "192.168.10.20" -- the HTML code for a double quote ("%22") is bracketing a legitimate IP. I wouldn't have caught this one, but the prior code parsed it out, so I saw it by comparing outputs. It's an interesting one, but only people worried about parsing HTML need to be concerned.
 
Upvote 0
Hi

Thank you for the report. I really appreciate it, it is a test with real data.

I will not change the code. I think these are exceptions and should be treated as such.

What I'd do:

Error 2 - I see nothing to do, it's a perfectly legal address, that it in this case it' not is purely contextual. These type of cases must be addressed on a one-to-one basis.

The others, however, can be addressed in a standard way.

If I had any of those problems I'd solve them by pre-processing the string, not changing the IP detection code.

Error 1 - I'd delete all the numbers preceeded by letters.

Error 3 - note that the problem is not just the "00", in accounting reports you could get 1.234.123.45 USD and that would be a perfectly valid IP address.
In this case I'd delete all the amounts, assuming I was able to tell them apart from other numbers or IP addresses, checking for example things like "USD" OR "EUR" or "£". That will depend on the specific report.

Error 4 - I'd replace things like %22 (quotes) or other codes that could preceed an IP address with spaces. Contrary to the other errors that I see as exceptions, this one may occur systematically in some reports, and so it may be really important to pre-process the string.

Notice that these are all very easy ways to address the problem.

Please don't think that it's because I don't want to change the code, it would be very easy. I really think that the way the code is designed makes it very easy to use by anyone and for any more special case, you can achieve your results by adequately pre-processing the input.

You could, of course, create your own specific function, encapsulating inside both the pre-processing of the string and the call to the IP extraction code.

Once again thank you for the report.
 
Upvote 0
This is the answer to the second problem. I did not change the code I posted before, I simply invoke it to extract the IPs out of the text of each cell.

This is the whole code.

Assuming the text in A2, down, you just have to run UniqueIPs().

Code: 
' PGC 200907
' UniqueIPs()
' Scans a range for unique IP addresses and
' writes the result as a comma separated string with all the unique IPs
Sub UniqueIPs()
Dim r As Range, rCell As Range
Dim sIPs As String, sIP As String, vIPs, vIP
Dim i As Integer, j As Integer
 
Set r = Range("A2", Range("A" & Rows.Count).End(xlUp))
 
With CreateObject("Scripting.Dictionary")
    For Each rCell In r
        If rCell <> "" Then
            ' extracts all the IPs found in the text of the cell
            sIPs = ExtractIPs(rCell.Text)
            If sIPs <> "" Then
                ' splits the IPs list into an array, each positions of the array 1 IP
                vIPs = Split(sIPs, ",")
                For i = 0 To UBound(vIPs)
                    ' removes 0 padding
                    vIP = Split(vIPs(i), ".")
                    For j = 0 To 3
                        vIP(j) = Val(vIP(j))
                    Next j
                    sIP = Join(vIP, ".")
                    ' adds the iP to the list of unique IPs
                    If Not .exists(sIP) Then .Add sIP, ""
                Next i
            End If
        End If
    Next rCell
    If .Count > 0 Then Range("B2") = Join(.keys, ", ")
End With
End Sub
 
' PGC 200907
' ExtractIPs(s as String)
' s - Text that may contain IP addresses
' Extracts all the IP addresses found in a string
' Returns a string that contains the IPs found, separated by commas
' Ex: s="IP1:1.2.3.4 called IP2:200.200.200.200" returns "1.2.3.4, 200.200.200.200"
' The IP address is a structure like ###.###.###.### where ### represents a number
' 0 to 255, with or without left padding zeros.
' Valid IP addresses are not extracted when followed or preceeded by a digit or when inside a
' dot number structure with more than 4 numbers like #.#.#.#.# where # represents a number.
Function ExtractIPs(s As String) As String
Const sOctP As String = "(?:25[0-5]|2[0-4]\d|[01]?\d{1,2})" ' Pattern for an octet, only valid 0 to 255
Const sIPAddrP As String = "(?:" & sOctP & "(?:\." & sOctP & "){3}(?!\d))" ' pattern for the IP address
Const s1OctIP As String = "(?:\d{4,}|[3-9]\d\d|2[6-9]\d|25[6-9])" ' invalid first octet
 
With CreateObject("VBScript.RegExp")
    .Pattern = "[\s\S]*?(?:\d+(?:\.\d+){4,}|" & s1OctIP & "|(" & sIPAddrP & "))|[\s\S]*"
    .Global = True
    ExtractIPs = Replace(Application.Trim(.Replace(s, " $1")), " ", ", ")
End With
End Function


Example:

<table border="1" cellpadding="1" style="background:#FFF; border-collapse:collapse;border-width:2px;border-color:#CCCCCC;font-family:Arial,Arial; font-size:10pt" ><tr><th style="border-width:1px;border-color:#888888;background:#9CF " > </th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >A</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" >B</th><th style="border-width:1px;border-color:#888888;background:#9CF; text-align:center" width=30 >C</th></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>1</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>2</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">There is a system at 192.168.23.142, but it's not communicat<br>ing with 192.168.4.132. Please call me at 1.555.432.2534 to <br>see if it's talking to 10.4.22.3. My IP is 172.16.4.12.</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">192.168.23.142, 192.168.4.132, 10.4.22.3, 172.16.4.12, 192.1<br>68.43.14</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>3</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">Here is a log:</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>4</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>5</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">192.168.23.142 2009/07/20 10:45:34 172.16.4.12</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>6</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">192.168.43.14 2009/07/20 10:45:35 172.16.04.12</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>7</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">192.168.23.142 2009/07/20 10:45:36 172.016.004.012</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>8</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:left;border-width: 1px;border-color:#888888; ">192.168.023.142 2009/07/20 10:45:38 172.016.4.012</td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;background:#9CF; text-align:center; " ><b>9</b></td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td><td style="border-width:1px;border-color:#000000; padding-left:0.5em; padding-top:0.4em; padding-right:0.5em; padding-bottom:0.25em;text-align:right;border-width: 1px;border-color:#888888; "> </td></tr><tr><td colspan=4 style="background:#9CF; padding-left:1em" > [IPExtract.xlsb]Sheet3</td></tr></table>
 
Upvote 0
pgc01 said:
Hi

Please don't think that it's because I don't want to change the code, it would be very easy. I really think that the way the code is designed makes it very easy to use by anyone and for any more special case, you can achieve your results by adequately pre-processing the input.
Click to expand...

Totally agree...

Re: the "second problem" of unique IPs.... I had a feeling I might not have been explicit enough in my description. I was preparing to come back online to clarify, and you had already posted code!

I meant to say that for **each time** you parse a single input, then the output string (a list of IPs) would be filtered for unique IPs; i.e., eliminating duplicates in each output. Your code would look through **all** inputs in my dataset (A2->down) and find all the unique IPs, correct? It turns out that I can use that function, too!...

I'm on a different computer than the one I have Jeanie HTML installed... I can send another reply, if you'd like to see it in that format though, but this is what I meant:

Input (A1): The log file said-- 192.168.32.23, 10.10.234.2. I also see another pair-- 192.168.32.23, 10.234.43.2. Go take a look at this.

Desired Output (B1): 192.168.32.23, 10.10.234.2, 10.234.43.2

Input (A2): I found these IPs listed in the message-- 10.34.33.22, 192.168.3.4, 10.34.33.22, 192.168.3.6, 192.168.3.10. Can you...

Desired Output (B2): 10.34.33.22, 192.168.3.4, 192.168.3.6, 192.168.3.10
 
Upvote 0
Hi

If you want the unique IPs in the text of 1 cell only then we can use an UDF.

Since there can be lots of duplicates, I thought it would make sense to sort the output. If you don't think that't interesting, you can simplify the code.

Try:

Code: 
' PGC 200907
' ExtractUniqIPs()
' Scans a string for unique IP addresses and
' Returns the result as a comma separated string with all the unique IPs
Function ExtractUniqIPs(s As String) As String
Dim sIPs As String, sIP As String, vIPs, vIP
Dim i As Integer, j As Integer
 
With CreateObject("Scripting.Dictionary")
    sIPs = ExtractIPs(s)
    If sIPs = "" Then Exit Function
 
    ' splits the IPs list into an array, each positions of the array 1 IP
    vIPs = Split(sIPs, ",")
    For i = 0 To UBound(vIPs)
        ' adds 0 padding
        vIP = Split(vIPs(i), ".")
        For j = 0 To 3
            vIP(j) = Format(vIP(j), "000")
        Next j
        sIP = Join(vIP, ".")
        ' adds the iP to the list of unique IPs
        If Not .exists(sIP) Then .Add sIP, ""
    Next i
    vIPs = .keys
End With
' sorts and removes 0 padding
QuickSort vIPs, LBound(vIPs), UBound(vIPs)
For i = 0 To UBound(vIPs)
    vIP = Split(vIPs(i), ".")
    For j = 0 To 3
        vIP(j) = Val(vIP(j))
    Next j
    vIPs(i) = Join(vIP, ".")
Next i
ExtractUniqIPs = Join(vIPs, ", ")
End Function

I used quicksort. That's good if you have many IP's. If you think that you'll always have just a few IP's you can replace it with anoter sorting method like bubblesort or even quicksort.

Code: 
Sub QuickSort(vArr As Variant, ByVal lLow As Long, ByVal lHigh As Long)
Dim vPivot As Variant, v As Variant
Dim lL As Long, lH As Long
 
vPivot = vArr((lLow + lHigh) / 2)
lL = lLow: lH = lHigh
 
While lL <= lH
    While vArr(lL) < vPivot: lL = lL + 1: Wend
    While vArr(lH) > vPivot: lH = lH - 1: Wend
    If lL <= lH Then
        v = vArr(lL): vArr(lL) = vArr(lH): vArr(lH) = v
        lL = lL + 1: lH = lH - 1
    End If
Wend
 
If lH > lLow Then QuickSort vArr, lLow, lH
If lL < lHigh Then QuickSort vArr, lL, lHigh
End Sub

Ex.:

In B2:

=ExtractUniqIPs(A2)


<TABLE style="BORDER-BOTTOM-COLOR: #cccccc; BORDER-RIGHT-WIDTH: 2px; BORDER-TOP-COLOR: #cccccc; BORDER-COLLAPSE: collapse; FONT-FAMILY: Arial,Arial; BACKGROUND: #fff; BORDER-TOP-WIDTH: 2px; BORDER-BOTTOM-WIDTH: 2px; BORDER-RIGHT-COLOR: #cccccc; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #cccccc; BORDER-LEFT-WIDTH: 2px" border=1 cellPadding=1><TBODY><TR><TH style="BORDER-BOTTOM-COLOR: #888888; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px"></TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px">A</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px">B</TH><TH style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: center; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px" width=30>C</TH></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">2</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">There is a system at 192.168.23.142, but it's not communicating with 192.168.4.132. Please call me at 1.555.432.2534 to see if it's talking to 10.4.22.3. My IP is 172.16.4.12.
Here is a log:

192.168.23.142 2009/07/20 10:45:34 172.16.4.12
192.168.43.14 2009/07/20 10:45:35 172.16.04.12
192.168.23.142 2009/07/20 10:45:36 172.016.004.012
192.168.023.142 2009/07/20 10:45:38 172.016.4.012
</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">10.4.22.3, 172.16.4.12, 192.168.4.132, 192.168.23.142, 192.168.43.14</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">3</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1.1.1.1 2.2.2.2 3.3.3.3 2.2.2.2 4.4.4.4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">1.1.1.1, 2.2.2.2, 3.3.3.3, 4.4.4.4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">4</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">200.200.200.200 100.100.100.100 000.000.000.000
000.000.000.000 200.200.200.200
</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">0.0.0.0, 100.100.100.100, 200.200.200.200</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">5</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">000.050.100.000 00.50.100.00 0.50.100.0 000.50.100.0</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: left; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">0.50.100.0</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="BORDER-BOTTOM-COLOR: #000000; TEXT-ALIGN: center; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #000000; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BACKGROUND: #9cf; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #000000; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em">6</TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD><TD style="BORDER-BOTTOM-COLOR: #888888; TEXT-ALIGN: right; PADDING-BOTTOM: 0.25em; BORDER-RIGHT-WIDTH: 1px; BORDER-TOP-COLOR: #888888; PADDING-LEFT: 0.5em; PADDING-RIGHT: 0.5em; BORDER-TOP-WIDTH: 1px; BORDER-BOTTOM-WIDTH: 1px; BORDER-RIGHT-COLOR: #888888; BORDER-LEFT-COLOR: #888888; BORDER-LEFT-WIDTH: 1px; PADDING-TOP: 0.4em"></TD></TR><TR><TD style="PADDING-LEFT: 1em; BACKGROUND: #9cf" colSpan=4>[IPExtract.xlsb]Sheet5</TD></TR></TBODY></TABLE>
 
Last edited:
Upvote 0
I'm glad it helped. I also wanted to have a base solution to deal with IP addresses. This will be a good starting point for similar problems.

Cheers!
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
please help me modify my vba code
Replies
1
Views
1K
roykana
R
W
  • Question Question
I would like these functions to calc an array of IP subnets. I Need a loop added to this function.
Replies
1
Views
2K
whaleh8er
W
R
  • Question Question
Extract specific data from .txt file into Excel using VBA
2 3 4
Replies
39
Views
27K
nassardewa
N
A
  • Question Question
how to loop data from different sheets,in VBA
Replies
0
Views
596
apeter5
A
L
  • Question Question
VBA excel 2010 how to initiate ssh connection from a list of ip address and send credential and write result of connection and credential in 2 cell
Replies
0
Views
14K
legethi
L

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

Share this page

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