Results 1 to 8 of 8
  1. #1
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12

    Get Only IP Address from a Text


    Hi,

    I have text values where written many things but i need only IP Number from given text, sample as below. please refer Result column which i needed from given text string.

    There are many combinations please refer Scenarios.

    Scenario Description Result
    1 to NOI-S5624P-SW-B3G1/0/25,172.17.4.6 172.17.4.6
    2 to NOIdsggs172.17.4.168-S5624P-SW-B3G1/0/25 172.17.4.168
    3 to GHA-S5624P-SW-B3172.17.14.106G1/0/25 172.17.14
    4 172.17.4.60to N-S5624P-SW-B3G1/0/25,172.17.4.6 172.17.4.6
    5 to NO-S5624P-SW-B3G1/172.17.4.6/25,172.17.4.6 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) 172.17.4.6
    7 forNetworkManagement172.170.147.6 172.170.147.6
    8 EXT_172.17.4.6For TTSL_VC_Noida_12006200065 172.17.4.6
    9 to GH172.17.4.6A-NE40E-AR-A3G7/0/0 172.17.4.6
    10 to GHA172.171.40.6-S5624F-SW-B3G1/0/22,172.17.4.6 172.171.40.6
    11 int_172.107.4.6APOLLO_172.17.4.6DCN 172.107.4.6

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    because its random, i dont see any 1 way to extricate the IP.

  3. #3
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    You'll need a function
    Code:
    Function FindIP(IP As String) As String
    
        Dim cnt              As Integer
        Dim NoOfDots         As Integer
        Dim i                As Integer
        Dim c                As Integer
    
    
        For i = 1 To Len(IP)
            If IsNumeric(Mid(IP, i, 1)) Or Mid(IP, i, 1) = "." Then
                cnt = cnt + 1
            Else
                'More than 6 consecutive char's that are
                'either numeric or dot (".")
                If cnt > 6 Then
                    FindIP = Mid(IP, i - cnt, cnt)
    
    
                    'Count the number of dot's
                    NoOfDots = 0
                    For c = 1 To Len(FindIP)
                        If Mid(FindIP, c, 1) = "." Then
                            NoOfDots = NoOfDots + 1
                        End If
                    Next c
    
    
                    'Check no of dots (s/b 3)
                    If NoOfDots = 3 Then
                        Exit Function
                    Else
                        FindIP = ""
                    End If
    
    
                End If
                cnt = 0
            End If
        Next i
    End Function
    Blatantly stolen from here https://www.mrexcel.com/forum/excel-...es-string.html

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I have modified the function found by Minty;
    imported the OP's data to table IPStuff
    and tested with OP's data:

    Modified function:
    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
        Dim cnt As Integer
        Dim NoOfDots As Integer
        Dim i As Integer
        Dim c As Integer
        Dim ShowChar As String 'for debugging
        Dim HoldString As String 'string being created with numerics and .
        Dim v
        Dim j As Integer
    
    10  Dim HasError As Boolean: HasError = False
    20  Dim ErrorSize As String: ErrorSize = " ****************************************** Error greater than 255  !!!!"
    30  On Error GoTo FindIP_Error
    
    40  For i = 1 To Len(IP)
    50      ShowChar = Mid(IP, i, 1)
    60      If IsNumeric(Mid(IP, i, 1)) Or Mid(IP, i, 1) = "." Then
    70          cnt = cnt + 1
    80          HoldString = HoldString & Mid(IP, i, 1)
    90      Else
                'More than 6 consecutive char's that are
                'either numeric or dot (".")
    100         HoldString = ""
    110         If cnt > 6 Then
    120             HoldString = Mid(IP, i - cnt, cnt)
                    'Count the number of dot's
    130             NoOfDots = 0
    140             For c = 1 To Len(HoldString)
    150                 If Mid(HoldString, c, 1) = "." Then
    160                     NoOfDots = NoOfDots + 1
    170                 End If
    180             Next c
                    'Check no of dots (s/b 3)
    190             If NoOfDots = 3 Then GoTo CheckVal
    200         End If
    210         cnt = 0
    220     End If
    230 Next i
    CheckVal:
    
    240 v = Split(HoldString, ".")
    250 For j = 0 To UBound(v) - 1
            ' Debug.Print v(j)
    260     If val(v(j)) > 255 Then HasError = True
    
    270 Next j
    280 FindIP = HoldString & IIf(HasError, ErrorSize, "")
    
    FindIP_Exit:
    290 Exit Function
    
    FindIP_Error:
    300 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure FindIP of Module AWF_Related"
    310 Resume FindIP_Exit
    
    End Function
    Test routine using a table with OP's data
    Code:
    '---------------------------------------------------------------------------------------
    ' Procedure : testGetIPInString
    ' Author    : mellon
    ' Date      : 07-Dec-2017
    ' Purpose   :Test routine to get IP from a string
    '---------------------------------------------------------------------------------------
    '
    Sub testGetIPInString()    '
        Dim r As DAO.Recordset
        
    10        On Error GoTo testGetIPInString_Error
    
    20    Set r = CurrentDb.OpenRecordset("IPStuff")
    30        Do While Not r.EOF
    40      If Len(r!Description & "") > 0 Then
    50          Debug.Print "Input is: " & r!Scenario & "  " & r!Description
                
                'use the function below "FindIP"
    60          Debug.Print FindIP(r!Description)
    70      End If
    80      r.MoveNext
    90        Loop
    
    testGetIPInString_Exit:
    100       Exit Sub
    
    testGetIPInString_Error:
    
    110     MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure testGetIPInString of Module AWF_Related"
    120     Resume testGetIPInString_Exit
    130
    End Sub
    Result:

    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 ****************************************** Error greater than 255  !!!!
    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.107.4.6APOLLO_172.17.4.6DCN
    172.107.4.6

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I would think that while walking through the "v" array, you could 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.

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    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  !!!!
    Last edited by orange; 12-07-2017 at 03:41 PM. Reason: adjustments based on RuralGuy comment

  7. #7
    Minty is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,001
    Neat modifications

  8. #8
    arjun5381 is offline Novice
    Windows 8 Access 2007
    Join Date
    May 2016
    Posts
    12
    Quote Originally Posted by orange View Post
    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  !!!!



    Thanks a lot you for your prompt support.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 06-27-2016, 12:28 PM
  2. Replies: 5
    Last Post: 04-15-2016, 07:52 AM
  3. Replies: 12
    Last Post: 01-18-2014, 11:35 AM
  4. Replies: 2
    Last Post: 08-22-2013, 12:02 AM
  5. using google maps address into a text field
    By thanosgr in forum Programming
    Replies: 5
    Last Post: 04-26-2012, 03:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums