We help IT Professionals succeed at work.

Extracting an IP address from a string

razorbackfan
razorbackfan used Ask the Experts™
on
How do I extract the IP address from a string in Excel (see attached file)
SampleData.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NoahHardware Tester and Debugger

Commented:
Hi there! :)

You forgot to attach the file.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
You may get the first IP address in each cell with:
=LEFT(A2,FIND(",",A2)-1)

Open in new window

Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you are looking to extract all of the IPv4 addresses, then you could use regular expressions in a VBA user defined function. You may use the UDF to return successive IP addresses by copying across a worksheet formula like shown below. It will return an empty string (looks like a blank) when the list is exhausted.
=IFERROR(ExtractIP($A2,COLUMNS($E2:E2)),"")

Open in new window


The code for the UDF must go in a regular module sheet, just like a recorded macro.
Function ExtractIP(Text As String, Instance As Long, Optional IP4 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
    If IP4 = True Then .Pattern = "\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}"
    .Global = True
    .IgnoreCase = True
    On Error Resume Next
    Set oMatches = .Execute(Text)
    ExtractIP = oMatches(Instance - 1)
End With
End Function

Open in new window

ExtractIP.xlsm
NoahHardware Tester and Debugger

Commented:
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))

Open in new window


To extract the rest of the string which is after the IP address:
=TRIM(RIGHT(A2,LEN(A2)-FIND(",",A2)))

Open 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.
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you want to capture ipv6 addresses, then you may change the formula to:
=IFERROR(ExtractIP($A2,COLUMNS($E2:E2),FALSE),"")

Open 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

Open in new window

ExtractIP.xlsm