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
NCS1	1.1.1.1, fe80::20c:29ff:fe21:d0da
DC1	fe80::d431:6c7a:9f1b:3fbb, 2.2.2.2
NCS1	1.1.1.1
DC1	2.2.2.2

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

</tbody>

Sheet1​

 

[LockeGarmin]

Cool approach! This ends up being slightly less accurate than using the Regex Pattern "(?:\d+\.){3}\d+" (Several preceding periods in the whole string would cause this to return a blank) and slightly slower (6.46 seconds vs 9.94 seconds over 1,000,000 iterations using the string "fe80::d431:6c7a:9f1b:3fbb, 2.2.2.2"). But this is definitely more suitable for a UDF, especially for most IP Address related Excel spreadsheet data. Thanks for sharing!

 

[Rick Rothstein]

Cool approach!

Thanks!

This ends up being slightly less accurate than using the Regex Pattern "(?:\d+\.){3}\d+" (Several preceding periods in the whole string would cause this to return a blank)

I am sure this will adversely affect execution speed (maybe dramatically), but does this version improve the accuracy...

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