Only need IPv4 Address

[tjreeddoc]

I need help with a Macro. I have a column data. Each Cell in the column has an IPv4 address and an IPv6 address. I only want the IPv4 address. What is the best way to leave the IPv4 address in this column?

B1 & B2 have the original information. I need the data to look like B3 & B4.

Thank you,

T.J.

Excel 2012

	A	B
1	NCS1	1.1.1.1, fe80::20c:29ff:fe21:d0da
2	DC1	fe80::d431:6c7a:9f1b:3fbb, 2.2.2.2
3
4	NCS1	1.1.1.1
5	DC1	2.2.2.2

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

 

[LockeGarmin]

You can run the Sub with Alt+F8 to convert the cells in Column B or you can use the function as a UDF in your Excel spreadsheet with =ExtractIpv4Address(B1)

Sub ExtractIpv4Addresses()   
 
Dim Rng As Range
Dim Cell As Range
Dim Address As String

    Set Rng = Range("B1:B" & ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row)
        
    For Each Cell In Rng
        Address = ExtractIp4Address(Cell.Value2)
        If Address <> vbNullString Then
            Cell.Value2 = Address
        End If
    Next Cell

End Sub


Function ExtractIp4Address(Source As String) As String

Static re As Object
    
    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        re.Pattern = "((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)"
    End If
    
    If re.Test(Source) Then
        ExtractIp4Address = re.Execute(Source).Item(0).Value
    End If
    
End Function

 

[Fennek]

Hi,

just a question: why don't you use the pattern:

"[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\."

regards

 

[LockeGarmin]

Hi!

That pattern (without the ending period token) would consider 999.999.999.999 as a valid IP Address. Chances are low the OP's spreadsheet won't have any invalid IP Addresses like that since it appeared generated, but a quick internet search made it pretty easy to throw in the more complicated pattern for an IP Address. If I wanted to quickly use a pattern for IP Addresses without doing a search and I was fairly confident on the correctness of the IP Addresses in the cells of my spreadsheet, I would have used pretty much the same pattern you did to save a little time.

I actually have a pattern saved at work for IP Addresses in case I need it (and I have a couple of times). Since I wasn't at work when I answered this I turned to the internet, but since I'm at work now here is the pattern I typically use:

(?:(?:2(?:5[0-5]|[0-4][0-9])|[0-1]?[0-9]{1,2})\.){3}(?:2(?:5[0-5]|[0-4][0-9])|[0-1]?[0-9]{1,2})

I saw this pattern when I was first learning Regex and after carefully reading it it really helped solidify my knowledge on how Regex works.

Good question!

 

[tjreeddoc]

LockeGarmin,

Thank you for your help!

You are correct! There will be no invalid IP addresses.
I have updated the Function with the new RegExp you found at work. Thank you!

However, I am getting the following when executing the Set Rng=Range step:

Run-time error ‘40036’
Application-defined or object-defined error

Any ideas?

T.J.

 

[LockeGarmin]

Hmm, I've looked around the internet for that error and most people are saying it doesn't have to do with the code.

One long shot might be to try replacing it with this line:

Set Rng = Excel.Range("B1:B" & Excel.ActiveSheet.UsedRange.Cells(Excel.ActiveSheet.UsedRange.Cells.Count).Row)

You might also check your library references (Tools -> References in the VB Editor) are any of them marked as "MISSING"?

Finally, the following post mentions uninstalling and reinstalling Excel worked for them.

https://groups.google.com/d/msg/microsoft.public.excel.programming/RnsyPr-ODb0/pg3j_LJ101MJ

Not really sure what else I can tell you. I can run this ok on my computer at home and work. Maybe another computer where you are at will be able to run this?

 

[Rick Rothstein]

You can run the Sub with Alt+F8 to convert the cells in Column B...

Sub ExtractIpv4Addresses()   
 
Dim Rng As Range
Dim Cell As Range
Dim Address As String

    Set Rng = Range("B1:B" & ActiveSheet.UsedRange.Cells(ActiveSheet.UsedRange.Cells.Count).Row)
        
    For Each Cell In Rng
        Address = ExtractIp4Address(Cell.Value2)
        If Address <> vbNullString Then
            Cell.Value2 = Address
        End If
    Next Cell

End Sub

I think this macro will be faster (especially if there are a huge number of rows to process). Also, if Column B's data is to be left unchanged, the output can be redirected to any location by changing the red highlighted address to the address of the first cell to receive the converted list.

Sub ExtractIp4Addresses()
  Dim R As Long, V As Variant, Data As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For Each V In Split(Data(R, 1), ",")
      If V Like "*.*" Then
        Data(R, 1) = Trim(V)
        Exit For
      End If
    Next
  Next
  Range("[B][COLOR="#FF0000"]B1[/COLOR][/B]").Resize(UBound(Data)) = Data
End Sub

or you can use the function as a UDF in your Excel spreadsheet with =ExtractIpv4Address(B1)....

Function ExtractIp4Address(Source As String) As String

Static re As Object
    
    If re Is Nothing Then
        Set re = CreateObject("VBScript.RegExp")
        re.Pattern = "((25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)\.){3}(25[0-5]|2[0-4][0-9]|[01]?[0-9][0-9]?)"
    End If
    
    If re.Test(Source) Then
        ExtractIp4Address = re.Execute(Source).Item(0).Value
    End If
    
End Function
[/QUOTE]
Here is a non-RegExp function (note I changed the name) that should also work...
[CODE]Function Ip4Address(Source As String) As String
  Dim V As Variant
  For Each V In Split(Source, ",")
    If V Like "*.*" Then
      Ip4Address = Trim(V)
      Exit For
    End If
  Next
End Function

 

[LockeGarmin]

I think this macro will be faster (especially if there are a huge number of rows to process). Also, if Column B's data is to be left unchanged, the output can be redirected to any location by changing the red highlighted address to the address of the first cell to receive the converted list.

Sub ExtractIp4Addresses()
  Dim R As Long, V As Variant, Data As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp))
  For R = 1 To UBound(Data)
    For Each V In Split(Data(R, 1), ",")
      If V Like "*.*" Then
        Data(R, 1) = Trim(V)
        Exit For
      End If
    Next
  Next
  Range("[B][COLOR=#FF0000]B1[/COLOR][/B]").Resize(UBound(Data)) = Data
End Sub

True! In keeping with modularity I would propose this as a more efficient implementation. This also includes the same proposed fixes from my previous post with the OP's current issue by including the Excel namespace. (You could replace the function with Rick's Ip4Address function as well)

Sub ExtractIp4Addresses()    Dim R As Long, Data As Variant
    
    Data = Excel.Range("B1", Excel.Cells(Excel.Rows.Count, "B").End(xlUp))
  
    For R = 1 To UBound(Data)
        Data(R, 1) = ExtractIp4Address(CStr(Data(R, 1)))
    Next
    
    Range("B1").Resize(UBound(Data)) = Data
End Sub

Here is a non-RegExp function (note I changed the name) that should also work...

Function Ip4Address(Source As String) As String
  Dim V As Variant
  For Each V In Split(Source, ",")
    If V Like "*.*" Then
      Ip4Address = Trim(V)
      Exit For
    End If
  Next
End Function

This would work for the OP's data, though I typically wouldn't recommend creating a UDF for such limited cases/datasets.

 

[Rick Rothstein]

Here is a non-RegExp function (note I changed the name) that should also work...

Function Ip4Address(Source As String) As String
  Dim V As Variant
  For Each V In Split(Source, ",")
    If V Like "*.*" Then
      Ip4Address = Trim(V)
      Exit For
    End If
  Next
End Function

This would work for the OP's data, though I typically wouldn't recommend creating a UDF for such limited cases/datasets.

Why not? It is my belief that VBA exists in Excel (and other Office products) so that users can add whatever functionality they need, no matter how small or limited it may be, to supplement Excel's core functionality. Now, to be clear, I was not touting my function as a generalized "find the Ip4 address" procedure. If one wanted a general solution, this is what I came up with off the top of my head...

[table="width: 500"]
[tr]
	[td]Function GetIp4(S As String, Optional StartAt As Long = 1) As String
  Dim X As Long, Z As Long, Parts() As String
  Parts = Split(Mid(S, StartAt), ".")
  For X = 1 To 3
    If Parts(X - 1) Like "*#" And Not Parts(X) Like "*[!0-9]*" And Not Parts(X + 1) Like "*[!0-9]*" And Parts(X + 2) Like "#*" Then
      For Z = Len(Parts(X - 1)) - 1 To 1 Step -1
        If Mid(Parts(X - 1), Z, 1) Like "[!0-9]" Then Parts(X - 1) = Mid(Parts(X - 1), Z + 1)
      Next
      For Z = 1 To Len(Parts(X + 2))
        If Mid(Parts(X + 2), Z, 1) Like "[!0-9]" Then Parts(X + 2) = Left(Parts(X + 2), Z - 1)
      Next
    End If
    For Z = 0 To 3
      GetIp4 = GetIp4 & "." & Parts(Z)
    Next
    GetIp4 = Mid(GetIp4, 2)
    Exit Function
  Next
End Function[/td]
[/tr]
[/table]

It is a little bit less efficient than the function quoted above (which is devoted solely to solving the OP's question) though, although that is to be expected of a fully generalized function. Note that I added an optional StartAt argument so that multiple Ip4 addresses could be located within a single text string.

 

[Rick Rothstein]

Why not? It is my belief that VBA exists in Excel (and other Office products) so that users can add whatever functionality they need, no matter how small or limited it may be, to supplement Excel's core functionality. Now, to be clear, I was not touting my function as a generalized "find the Ip4 address" procedure. If one wanted a general solution, this is what I came up with off the top of my head...

[table="width: 500"]
[tr]
	[td]Function GetIp4(S As String, Optional StartAt As Long = 1) As String
  Dim X As Long, Z As Long, Parts() As String
  Parts = Split(Mid(S, StartAt), ".")
  For X = 1 To 3
    If Parts(X - 1) Like "*#" And Not Parts(X) Like "*[!0-9]*" And Not Parts(X + 1) Like "*[!0-9]*" And Parts(X + 2) Like "#*" Then
      For Z = Len(Parts(X - 1)) - 1 To 1 Step -1
        If Mid(Parts(X - 1), Z, 1) Like "[!0-9]" Then Parts(X - 1) = Mid(Parts(X - 1), Z + 1)
      Next
      For Z = 1 To Len(Parts(X + 2))
        If Mid(Parts(X + 2), Z, 1) Like "[!0-9]" Then Parts(X + 2) = Left(Parts(X + 2), Z - 1)
      Next
    End If
    For Z = 0 To 3
      GetIp4 = GetIp4 & "." & Parts(Z)
    Next
    GetIp4 = Mid(GetIp4, 2)
    Exit Function
  Next
End Function[/td]
[/tr]
[/table]

It is a little bit less efficient than the function quoted above (which is devoted solely to solving the OP's question) though, although that is to be expected of a fully generalized function. Note that I added an optional StartAt argument so that multiple Ip4 addresses could be located within a single text string.

This version of the function is probably a little better...

[table="width: 500"]
[tr]
	[td]Function GetIp4(S As String, Optional StartAt As Long = 1) As String
  Dim X As Long, Z As Long, Parts() As String
  Parts = Split(Mid(S, StartAt), ".")
  On Error GoTo SomethingWrong
  For X = 1 To 3
    If Parts(X - 1) Like "*#" And Not Parts(X) Like "*[!0-9]*" And Not Parts(X + 1) Like "*[!0-9]*" And Parts(X + 2) Like "#*" Then
      For Z = Len(Parts(X - 1)) - 1 To 1 Step -1
        If Mid(Parts(X - 1), Z, 1) Like "[!0-9]" Then Parts(X - 1) = Mid(Parts(X - 1), Z + 1)
      Next
      For Z = 1 To Len(Parts(X + 2))
        If Mid(Parts(X + 2), Z, 1) Like "[!0-9]" Then Parts(X + 2) = Left(Parts(X + 2), Z - 1)
      Next
      GetIp4 = Parts(X - 1) & "." & Parts(X) & "." & Parts(X + 1) & "." & Parts(X + 2)
      Exit For
    End If
  Next
SomethingWrong:
End Function[/td]
[/tr]
[/table]