Good call Allan.
Here's a revised function. Also note, I modified the last test case to ensure a test of the value of a 3 digit component
Code:
Scenario |
Description |
1 |
to NOI-S5624P-SW-B3G1/0/25,172.17.4.6 |
2 |
to NOIdsggs172.17.4.168-S5624P-SW-B3G1/0/25 |
3 |
to GHA-S5624P-SW-B3172.17.14.106G1/0/25 |
4 |
172.17.4.60to N-S5624P-SW-B3G1/0/25,172.17.4.6 |
5 |
to NO-S5624P-SW-B3G1/172.17.4.6/25,172.17.4.6 |
6 |
(NA/172.17.4.6BACKBONE/To DEL-172.17.4.1666CON-NE40E-X16-BR-A5[Gi1/1/6]//1G//Silver Protected) |
7 |
forNetworkManagement172.170.147.6 |
8 |
EXT_172.17.4.6For TTSL_VC_Noida_12006200065 |
9 |
to GH172.17.4.6A-NE40E-AR-A3G7/0/0 |
10 |
to GHA172.171.40.6-S5624F-SW-B3G1/0/22,172.17.4.6 |
11 |
int_172.307.4.6APOLLO_172.17.4.6DCN -jed added the 3 |
Code:
Function FindIP(IP As String) As String
'from https://www.accessforums.net/showthread.php?t=69462 where minty gave this link
'https://www.mrexcel.com/forum/excel-questions/404815-vba-parsing-multiple-ip-addresses-string.html
'
'modified 7-Dec-2017
'This will give an error if any part of the IP is greater than 255
'additional mod 7-Dec-2107
'From RuralGuy
'check for Len() > 3 and then use Right(v(j),3) with I believe the correct assumption
'that we have picked up the tail end of a string of numbers. You could than perform the Val() test.
Dim cnt As Integer
Dim NoOfDots As Integer
Dim i As Integer
Dim c As Integer
Dim ShowChar As String
Dim HoldString As String
Dim v
Dim j As Integer
Dim ErrorID As Integer
10 Dim HasError As Boolean: HasError = False
20 Dim IPError(1) As String
30
40 IPError(0) = " ****************** 0 " & vbTab & "********************* Error more than 3 digits !!!!"
50 IPError(1) = " ****************** 1 " & vbTab & "********************* Error greater than 255 !!!!"
60
On Error GoTo FindIP_Error
70 For i = 1 To Len(IP)
80 ShowChar = Mid(IP, i, 1)
90 If IsNumeric(Mid(IP, i, 1)) Or Mid(IP, i, 1) = "." Then
100 cnt = cnt + 1
110 HoldString = HoldString & Mid(IP, i, 1)
120 Else
'More than 6 consecutive char's that are
'either numeric or dot (".")
130 HoldString = ""
140 If cnt > 6 Then
150 HoldString = Mid(IP, i - cnt, cnt)
'Count the number of dot's
160 NoOfDots = 0
170 For c = 1 To Len(HoldString)
180 If Mid(HoldString, c, 1) = "." Then
190 NoOfDots = NoOfDots + 1
200 End If
210 Next c
'Check no of dots (s/b 3)
220 If NoOfDots = 3 Then GoTo CheckVal
230 End If
240 cnt = 0
250 End If
260 Next i
CheckVal:
270 v = Split(HoldString, ".")
280 For j = 0 To UBound(v) - 1
' Debug.Print v(j)
290 If Len(v(j)) > 3 Then
300 HasError = True
310 ErrorID = 0
320 Exit For
330 End If
340 If val(v(j)) > 255 Then
350 HasError = True
360 ErrorID = 1
370 End If
380 Next j
390 FindIP = HoldString & IIf(HasError, IPError(ErrorID), "")
FindIP_Exit:
400 Exit Function
FindIP_Error:
410 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure FindIP of Module AWF_Related"
420 Resume FindIP_Exit
End Function
New results:
Code:
Input is: 1 to NOI-S5624P-SW-B3G1/0/25,172.17.4.6
172.17.4.6
Input is: 2 to NOIdsggs172.17.4.168-S5624P-SW-B3G1/0/25
172.17.4.168
Input is: 3 to GHA-S5624P-SW-B3172.17.14.106G1/0/25
3172.17.14.106 ****************** 0 ********************* Error more than 3 digits !!!!
Input is: 4 172.17.4.60to N-S5624P-SW-B3G1/0/25,172.17.4.6
172.17.4.60
Input is: 5 to NO-S5624P-SW-B3G1/172.17.4.6/25,172.17.4.6
172.17.4.6
Input is: 6 (NA/172.17.4.6BACKBONE/To DEL-172.17.4.1666CON-NE40E-X16-BR-A5[Gi1/1/6]//1G//Silver Protected)
172.17.4.6
Input is: 7 forNetworkManagement172.170.147.6
172.170.147.6
Input is: 8 EXT_172.17.4.6For TTSL_VC_Noida_12006200065
172.17.4.6
Input is: 9 to GH172.17.4.6A-NE40E-AR-A3G7/0/0
172.17.4.6
Input is: 10 to GHA172.171.40.6-S5624F-SW-B3G1/0/22,172.17.4.6
172.171.40.6
Input is: 11 int_172.307.4.6APOLLO_172.17.4.6DCN -jed added the 3
172.307.4.6 ****************** 1 ********************* Error greater than 255 !!!!