CODEWITHSUNDEEP
vbaexceloutput
yermander
yermander

Reputation: 67

Need to tweak Excel function that uses regex to extract IP addresses from job output

I have a job output that reads like this...

Node number 1 (172.xxx.123.210):
ERROR: Cannot download Running config: Connection Refused by 172.xxx.123.210

Node number 2 (172.xxx.124.162):
ERROR: Cannot download Running config: Connection Refused by 172.xxx.124.162

I want to get a handle on those IP addresses in brackets that are failing the job. I've cobbled together a VB function that contains a regex to extract the IP's and it is working great. The catch is though, that the regex ^.*\((\d.*)\) is looking for a sequence of characters within brackets such as...

Node number 1 (172.123.123.210):

and when I call the function on a line that doesn't contain brackets it just returns a zero. I haven't got the reputation points to allow me to post an image so you could quickly try it if you like to see the output!

I don't want to return a zero if there isn't an IP in brackets I'd rather it just left the cell empty. Any suggestions about how I can tweak my function to do this?

Here's the function I am using...

Function getIP(info As String)

Dim allMatches As Object
Dim RE As Object
Set RE = CreateObject("vbscript.regexp")

RE.Pattern = "^.*\((\d.*)\)"
RE.Global = True
RE.IgnoreCase = True

Set allMatches = RE.Execute(info)

If (allMatches.Count <> 0) Then
    result = allMatches.Item(0).SubMatches.Item(0)

End If

getIP = result
End Function

Upvotes: 2

Views: 4139

Answers (2)

Wightboy
Wightboy

Reputation: 261

I found that using two regular expression patterns allows recognition of IP addresses with or without brackets:

Private Sub CommandButton1_Click()

Set regEx = CreateObject("vbscript.regexp")
Set regEx2 = CreateObject("vbscript.regexp")

regEx.Global = True
regEx.IgnoreCase = True
regEx.Pattern = "\(([0-9]{3}).([0-9]{3}).([0-9]{3}).([0-9]{3})\)"
regEx2.Global = True
regEx2.IgnoreCase = True
regEx2.Pattern = "([0-9]{3}).([0-9]{3}).([0-9]{3}).([0-9]{3})"

For i = 1 To UsedRange.Rows.Count
    Set allMatches = regEx.Execute(Cells(i, 1).Value)
    Set otherMatches = regEx2.Execute(Cells(i, 1).Value)
    If allMatches.Count <> 0 Then
        Cells(i, 1).Interior.ColorIndex = 33
    End If
    If otherMatches.Count <> 0 Then
        Cells(i, 1).Interior.ColorIndex = 33
    End If
Next i

End Sub

This will loop through column 1, where I wrote some IP addresses, and highlight all valid IP addresses with or without brackets and leave all non-IP addresses blank.

I found when including both versions of the regular expression with an OR it didn't work and I'm not sure why, maybe something to do with de-limiting the brackets?

Hope you find this useful.

Upvotes: 2

Andrei Tătar
Andrei Tătar

Reputation: 8295

Simple IP Pattern: (?:[0-9]{1,3}.){3}[0-9]{1,3} Note that this pattern will also match "999.999.999.999". So it doesn't detect valid IPs.

The following code:

var regex = new Regex(@"(?:[0-9]{1,3}\.){3}[0-9]{1,3}");
var ip = regex.Match("Node number 1 (172.123.123.210)").Value;
Console.WriteLine(ip);

Outputs:

172.123.123.210

I'm not really sure of VBA syntax

Upvotes: 0

Related Questions