Extracting an IP address from a string

Hi there! :)

You forgot to attach the file.

You may get the first IP address in each cell with:

=LEFT(A2,FIND(",",A2)-1)

Select allOpen in new window

Hi there! :)

You may refer to the following example file: SampleData.xlsx

To extract the first part of the string which is the IP address:

=TRIM(LEFT(A2,FIND(",",A2)-1))

Select allOpen in new window

To extract the rest of the string which is after the IP address:

=TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2)))

Select allOpen in new window

The part of the string which was used to separate these two parts was the first comma. I also put the TRIM function just in case there are any unwanted spaces at the front or the back of the extracted data.

If you want to capture ipv6 addresses, then you may change the formula to:

=IFERROR(ExtractIP($A2,COLUMNS($E2:E2),FALSE),"")

Select allOpen in new window

And the code (works for either ipv4 or ipv6) would be:

Function ExtractIP(Text As String, Instance As Long, Optional IPv4 As Boolean = True) As String
Static RegEx As Object
Dim oMatch As Object, oMatches As Object
If RegEx Is Nothing Then Set RegEx = CreateObject("VBScript.RegExp")

With RegEx
    .Pattern = IIf(IPv4, "\b\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\b", "\b(?:[A-F0-9]{0,4}:){5,7}[A-F0-9]{1,4}\b")
    .Global = True
    .IgnoreCase = True
    On Error Resume Next
    Set oMatches = .Execute(Text)
    ExtractIP = oMatches(Instance - 1)
End With
End Function

Select allOpen in new window

ExtractIP.xlsm