Seperating and Filtering IP Addresses in Excel

Hello Experts.

I need some assistance.
I'm using RVTools to extract information about all our VC servers.
The networking tab, has a column called "IP Addresses". Problem is, it shows ALL the IP Addresses of the server delimited by commas (IPv6, IPv4, Internal Addresses & External Addresses).
I need Excel to do the following:

1. Remove all IPv6 Addresses

2. divide into two columns, Internal Addresses and External Addresses delimited by commas

3. After each of the previously added column, another column will be added with the count of each address type

So for a server with the following IP addresses: [10.7.0.200,10.23.25.155,10.23.91.56,91.202.169.245,91.202.169.45] I'll end up with four colums:
[10.7.0.200,10.23.35.155,10.23.91.56] [3] [91.202.169.245,91.202.169.45] [2]
LVL 8
David SankovskySenior SysAdminAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Please define how (you would like) to determine:

a) IPv6 Addresses
b) IPv4 Addresses
c) Internal Addresses
d) External Addresses

Also, providing some further sample input, & the corresponding output other than that which you have already mentioned above, will be helpful, as the example provided makes little sense (from the information in your question).

Input:
10.7.0.200,
0.23.25.155,
10.23.91.56,
91.202.169.245,
91.202.169.45

Output:
[10.7.0.200,10.23.35.155,10.23.91.56] [3] <- 3 of which type?
[91.202.169.245,91.202.169.45] [2] <- 2 of which type?

Thank you.
0
Saqib Husain, SyedEngineerCommented:
Can you upload a sample file with a few lines of addresses showing the required result. Also an explanation of how to differentiate between IPv6, IPv4, Internal Addresses & External Addresses would be helpful.
0
David SankovskySenior SysAdminAuthor Commented:
Hi, Sorry, Let me clarify..
and IPv4 Address is defined as an address made of four numbers (between 1 and 255) divided by dots. (i.e X.X.X.X Where X is anywhere between 1 and 255 in each and every one) everything that does not conform to this structure is not IPv4.
As for distinguishing between internal and external addresses.. that's a bit more tricky, but essentially anything that's in the following format is internal:
10.X.X.X
192.168.X.X
and the following range as well: 172.16-31.X.X (meaning the 2nd number can be anything between 16-31)
in all the cases X is anything between 1 and 255.
I hope this makes sense
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

[ fanpages ]IT Services ConsultantCommented:
Thanks.

Are you absolutely sure that none of the numbers in the number-dotted arrangement can be 0?  That is, 0 to 255, not 1 to 255, as you have stated above.

...or is this something that can be discounted just for your purposes?

Additionally, would any addresses including a number outside of the range (1..255, or 0...255, depending on your response to my above query), be considered 'invalid', & should be ignored from the results?

Finally, are you planning to upload a sample file, like Syed suggested?
0
David SankovskySenior SysAdminAuthor Commented:
Hi,
0-255 isn't a problem and actually I should've been more percise, 0 is possible but only in the 2nd and third numbers, the first and last numbers will never contain that number - it simply can't.. in the world of networking it's considered an invalid address, but since I'm pulling the information from a production environment, all the IPs are valid for certain, so we can safely make the search parameter 0-255 if it makes it easier for you
Regarding a sample file, It might take me some time to make one, as I'm not in front of my computer and won't be for a couple of hours.
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Hi David,

Please find the attached workbook with two UDF's I used long time ago for almost the same task.
In the attached workbook, on Module1, you will find two UDF's InternalIP() and ExternalIP() which are used in B1 and D1 on Sheet1.

See if these udf's are helpful to you also.
IP-Addresses.xlsm
1
David SankovskySenior SysAdminAuthor Commented:
That's exactly what I needed in terms of screening out internal and external, perfectly designed!
The only problem is, the catches IPv6 Addresses as external ones
Any way to scrren them out before applying the UDFs? or make the screening part of the UDF?
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
I think I didn't get your question correctly.
You haven't provided any details of IPv6, do you want to exclude them from the output?
It would be helpful if you upload the workbook back with the desired output.

Also please edit your question to add the proper topics.
0
David SankovskySenior SysAdminAuthor Commented:
Apologies, Yes, I need the file to completely ignore IPv6 Addresses.
Also, for some reason when I tested a case when the order was not explicit but mixed (internal, then external and then another internal) the external address count was wrong, it was larger by one.
I added a file with two tabs, the first is the output made by the functions you provided, the other is the desired result.
IP-Addresses.xlsm
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay give me some time as I am going to take a break. I will look at your file once I am back.
0
[ fanpages ]IT Services ConsultantCommented:
Hi David,

As sktneer took an approach using "Regular Expressions", I thought I would offer a solution another way.

Please find attached the latest workbook within this thread (to date), with a new worksheet, [Q_28705940].

I have added a new code module, "basQ_28705940", that contains the subroutine below:

Option Explicit
Public Sub Q_28705940()

' -------------------------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/questions/28705940/Seperating-and-Filtering-IP-Addresses-in-Excel.html ]
'
' Question Channel: Experts Exchange > Questions > Seperating and Filtering IP Addresses in Excel
' Topic Area:       [ http://www.experts-exchange.com/topics/ms-excel/ ]
'
' ID:               Q_28705940
' Question Title:   Seperating [sic] and Filtering IP Addresses in Excel
' Question Dated:   2015-08-16 09:46 AM
' Question Asker:   David Sankovsky
' Asker Profile:    [ http://www.experts-exchange.com/members/Davsank.html ]
' Attachment #1:    [ http://filedb.experts-exchange.com/incoming/2015/08_w34/929742/IP-Addresses.xlsm ]
' Attachment #2:    [ http://filedb.experts-exchange.com/incoming/2015/08_w34/929745/IP-Addresses.xlsm ]
'
' Solution posted:  16 August 2015 by fanpages
' Expert Profile:   [ http://www.experts-exchange.com/members/fanpages.html ] | [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2015 Clearlogic Concepts (UK) Limited                                            [ http://NigelLee.info ]
' -------------------------------------------------------------------------------------------------------------------------------

  Dim blnInternal                                       As Boolean
  Dim blnOK                                             As Boolean
  Dim lngLoop                                           As Long
  Dim lngNumber                                         As Long
  Dim lngRow                                            As Long
  Dim strAddress                                        As String
  Dim strInput                                          As String
  Dim strExternal()                                     As String
  Dim strInternal()                                     As String
  Dim vntAddress                                        As Variant
  Dim vntSplit                                          As Variant
  
  On Error GoTo Err_Q_28705940
  
' Requirements:
'
' IPv4 Address is defined as an address made of four numbers (between 1 and 255) divided by dots.
' (i.e X.X.X.X Where X is anywhere between 1 and 255 in each and every one) everything that does not conform to this structure is not IPv4.
'
' essentially anything that's in the following format is internal:
' 10.X.X.X
' 192.168.X.X
' and the following range as well: 172.16-31.X.X (meaning the 2nd number can be anything between 16-31)
' in all the cases X is anything between 1 and 255.
'
' 0 is possible but only in the 2nd and third numbers, the first and last numbers will never contain that number -
' it simply can't.. in the world of networking it's considered an invalid address, but since I'm pulling the information from a production environment,
' all the IPs are valid for certain, so we can safely make the search parameter 0-255 if it makes it easier for you

  Worksheets("Q_28705940").Select
  
  Application.ScreenUpdating = False
  
  For lngRow = 2& To Cells(Cells.Rows.Count, 1).End(xlUp).Row                                           ' Loop from the second row to the last row in column [A]...
  
      Erase strExternal()
      ReDim strExternal(0&) As String
      
      Erase strInternal()
      ReDim strInternal(0&) As String
      
      strInput = Cells(lngRow, 1)
      
      If Len(Trim$(strInput)) > 0 Then                                                                  ' If there is a value in column [A] on the current row...
         If InStr(strInput, ".") > 0 Then                                                               ' If the value contains at least one period "." (dot) character...
            vntSplit = Split(strInput, ",")                                                             ' Split the value into sub-sections, delimited by a comma...
            
            For lngLoop = 0& To UBound(vntSplit)                                                        ' Loop through each of the sub-sections of the input...
                
                blnOK = False
                
                vntAddress = Split(Trim$(vntSplit(lngLoop)), ".")                                       ' Split the sub-section into each number separated by a period...
                
                blnOK = (UBound(vntAddress) = 3&)                                                       ' Check that there are four (zero relative) parts
                
                If (blnOK) Then
                   For lngNumber = 0& To UBound(vntAddress)                                             ' Loop through each of the four parts, checking the range of values of each
                   
                       blnOK = False
                       
                       Select Case (False)
                       
                           Case (IsNumeric(vntAddress(lngNumber)))                                      ' Check the part is a number
                           Case (CLng(vntAddress(lngNumber)) >= Choose(lngNumber + 1&, 1&, 0&, 0&, 1&)) ' Greater than/equal to 1 (1st/4th) or 0 (2nd/3rd)
                           Case (CLng(vntAddress(lngNumber)) <= 255&)                                   ' Less than, or equal to, 255
                           
                           Case Else
                               blnOK = True                                                             ' If all True, this is a valid IP(v4) Address
                               
                       End Select ' Select Case (False)
                       
                       If Not (blnOK) Then                                                              ' If any checks are False, ignore the rest
                          Exit For
                       End If ' If Not (blnOK) Then
                       
                   Next lngNumber                                                                       ' Loop to check the validity of the next part
                End If ' If (blnOK) Then
                
                If (blnOK) Then                                                                         ' All OK, check if Internal or External...
                   strAddress = Trim$(vntSplit(lngLoop))
                   blnInternal = False                                                                  ' Assume External, until proven otherwise
                   
                   Select Case (True)
                   
                       Case (strAddress Like "10.?*.?*.?*"), _
                            (strAddress Like "192.168.?*.?*")
                           blnInternal = True
                       
                       Case (strAddress Like "172.??.?*.?*")
                           blnInternal = (CLng(vntAddress(1&)) >= 16& And CLng(vntAddress(1&)) <= 31&)  ' Internal only if 2nd digit is in range [16..31]
                       
                       Case Else
                           
                   End Select ' Select Case (CStr(vntSplit(lngLoop)))
                   
                   If (blnInternal) Then                                                                ' If Internal, add to the Internal results
                      ReDim Preserve strInternal(UBound(strInternal) + 1&) As String
                      strInternal(UBound(strInternal)) = strAddress
                   Else                                                                                 ' Otherwise, add to the External results
                      ReDim Preserve strExternal(UBound(strExternal) + 1&) As String
                      strExternal(UBound(strExternal)) = strAddress
                   End If ' If (blnInternal) Then
                End If ' If (blnOK) Then
                
            Next lngLoop ' For lngLoop = 0& To UBound(vntSplit)                                         ' Loop to the next sub-section of the input
            
         End If ' If Instr(strInput, ".") > 0 Then
      End If ' If Len(Trim$(strInput)) > 0 Then
      
      Range(Cells(lngRow, "B"), Cells(lngRow, "E")) = Array(Mid$(Trim$(Join(strInternal, ", ")), 3), _
                                                            UBound(strInternal), _
                                                            Mid$(Trim$(Join(strExternal, ", ")), 3), _
                                                            UBound(strExternal))                        ' Output results to columns [B], [C], [D], & [E]
                                                            
      Range(Cells(lngRow, "B"), Cells(lngRow, "E")).NumberFormat = Array("@", _
                                                                         "0;-0;#", _
                                                                         "@", _
                                                                         "0;-0;#")                      ' Set NumberFormat on these columns
      
  Next lngRow                                                                                           ' Loop to the next row
  
Exit_Q_28705940:

  On Error Resume Next
  
  Erase strExternal()
  ReDim strExternal(0&) As String

  Erase strInternal()
  ReDim strInternal(0&) As String
  
  Set vntAddress = Nothing
  Set vntSplit = Nothing
  
  Application.ScreenUpdating = True
  
  Exit Sub
  
Err_Q_28705940:

  MsgBox "Error #" & CStr(Err.Number) & _
         vbCrLf & vbLf & _
         Err.Description, _
         vbExclamation Or vbOKOnly, _
         ThisWorkbook.Name
         
  Resume Exit_Q_28705940
  
End Sub

Open in new window


When this routine is executed, the output is recorded within the [Q_28705940] worksheet in columns [B:E] depending on the corresponding "Input" value in column [A] of the same row.
Q-28705940.xlsm
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached workbook and see if you get the desired output.
IP-Addresses-v1.xlsm
1
aikimarkCommented:
I got here too late :-(
I was going to go the regexp solution route, like sktneer.
0
aikimarkCommented:
Oh, what the heck.  I decided to have some Regexp fun on a Sunday evening.  I added a couple of functions.
Option Explicit

Function InternalIP(parmString As String)
    Static oRE As Object
    Dim oM As Object, oMatches As Object
    Dim strIP As String
    
    If oRE Is Nothing Then
        Set oRE = CreateObject("VBScript.RegExp")
        oRE.Global = True
        oRE.Pattern = "\b((?:10\.\d+\.\d+\.\d+)|(?:192\.168\.\d+\.\d+)|(?:172\.(?:[1][6-9]|[2][0-9]|[3][0-1])\.\d+\.\d+))\b"
    End If
    
    Set oMatches = oRE.Execute(parmString)
    For Each oM In oMatches
        strIP = strIP & ", " & oM.submatches(0)
    Next oM
    InternalIP = Mid(strIP, 3)
End Function

Function ExternalIP(parmString As String) As String
    Static oRE As Object
    Dim oM As Object, oMatches As Object
    Dim strResult As String
    Dim i As Long, p As Long
    If oRE Is Nothing Then
        Set oRE = CreateObject("VBScript.RegExp")
        oRE.Global = True
        oRE.Pattern = "\b(\d+\.\d+\.\d+\.\d+)\b"
    End If
    
    Set oMatches = oRE.Execute(parmString)
    For Each oM In oMatches
        If IsInternalIP(oM.submatches(0)) Then
        Else
            strResult = strResult & ", " & oM.submatches(0)
        End If
    Next
    ExternalIP = Mid(strResult, 3)
End Function

Function IPAddressCount(parmString As String) As Long
    Static oRE As Object
    Dim oMatches As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("VBScript.RegExp")
        oRE.Global = True
        oRE.Pattern = "\b(\d+\.\d+\.\d+\.\d+)\b"
    End If
    Set oMatches = oRE.Execute(parmString)
    IPAddressCount = oMatches.Count
End Function

Function IsInternalIP(parmString As String) As Boolean
    Static oRE As Object
    If oRE Is Nothing Then
        Set oRE = CreateObject("VBScript.RegExp")
        oRE.Global = True
        oRE.Pattern = "\b((?:10\.\d+\.\d+\.\d+)|(?:192\.168\.\d+\.\d+)|(?:172\.([1][6-9]|[2][0-9]|[3][0-1])\.\d+\.\d+))\b"
    End If
    IsInternalIP = oRE.test(parmString)
End Function

Open in new window

Now C2 is now =ipaddresscount(B2)
and E2 is now =ipaddresscount(D2)

The results of this code seems to be same as that in the Required worksheet.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
David SankovskySenior SysAdminAuthor Commented:
That one did the trick, Perfect code, Thank you very much!
0
[ fanpages ]IT Services ConsultantCommented:
All suggestions within this thread will "do the trick".

This will be my first & my last response to you, David; if you do not even acknowledge my proposal to address your question.

I expect sktneer feels equally slighted, although, to be fair, you did actually look at his suggestion.
2
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@fanpages

Thanks for the insight. :)

Actually David rewarded aikimark for getting here late. lol
This also shows he is not capable of recognizing the help correctly.

Honestly I list all such askers in the Ignore List and don't prefer to answer them in their future questions. :)
1
[ fanpages ]IT Services ConsultantCommented:
No problem, sktneer.

Also, for the record, I hold no grudges or issues with you, aikimark.

Have a good day everybody.
1
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Also, for the record, I hold no grudges or issues with you, aikimark.

Me too. :)
1
aikimarkCommented:
@David Sankovsky

Did you mean to accept my comment as the only solution to your question?  My comment was not the first you were testing and was meant to offer later readers more regexp examples.

We can reopen the question, allowing you to accept multiple comments.
0
David SankovskySenior SysAdminAuthor Commented:
I am trying, I've been trying ever since I accidentally closed it only on your solution.. but I see no "edit" or "reopen" button anywhere..
0
David SankovskySenior SysAdminAuthor Commented:
Thanks! I was trying to edit this for ages.
0
[ fanpages ]IT Services ConsultantCommented:
Sorry David.

Accepting my (or, indeed, anybody else's) solution was not the motive behind my comment earlier.

Thank you for progressing with closure again though.

My point was that it struck me as rude that you did not even acknowledge my proposal, not even a "Thanks, but no thanks".

It was far from a trivial amount of effort, especially ensuring I commented the lines so you could follow them if your requirements ever changed in the future.

I appreciate you coming back & closing, but what solution did you actually progress with using?
0
David SankovskySenior SysAdminAuthor Commented:
Hi [ fanpages ],

I eventually used aikimark's solution for several reasons.
It was divided into several sub-functions that were actually easier to follow and easier to integrate into the main script I'm running (The action of dividing the list into internal and external is only part of what I'm eventually trying to achieve). Further, your subroutine, while working absolutely  correctly, can't be easily modified by someone who isn't proficient in VBA programming, and is limited to a certain workbook, while several smaller functions, can be called anywhere that I might need provide much greater flexibility.

As for sktneer's code, while indeed using functions, they couldn't correctly deal with mixed lists (where the addresses weren't arranged as internal first and external later), once he had fixed this issue, the function he used to count the addresses in each instance were always 1 higher or 1 lower than the actual result.

aikimark's solution was simply the best suited for the task at hand.
0
[ fanpages ]IT Services ConsultantCommented:
Hi David,

That is indeed a good reason to use that code.

I am not sure why you believe my proposal could not be ported elsewhere, but obviously none of us were aware that this would be your ultimate goal when you stated your requirements.

I was coming from an angle where, if you had many, many numbers of rows of data, calling four separate functions may hinder performance, so I opted to process all the necessary output at once.


Everybody has different strengths & weaknesses, & you may find it easier to read "regular expression" syntax versus Visual Basic for Applications code (regardless of the comments I added).

No need to dwell on this though.   I'm not here for points, I'm just here to help.

If you do feel you need to return to one/other solution in the future, please just comment again (or raise a new question referencing this one).

Thanks again for taking the time to participate, & to explain your decision :)
0
aikimarkCommented:
@David

Thanks for the points, sticking with the question, and the multi-accept closure.

==============
@sktneer
This also shows he is not capable of recognizing the help correctly.
Please understand that David has only been an EE member since February and this is only his 26th question.  Try to remember your learning curve and work with newbies, like David, to get them up to speed.  If it makes you feel any better, I started with your VBA code and your regex patterns.

You and fanpages did great analytical work, extracting the detailed requirements to David's problems.  The two of you deserve points for that part of the solution, not just my VBA code.  The great thing about places like EE is that wonderful solutions can come from collaboration like we see in this thread.

In a way, I'm kind of like BASF.  I make things better  :-)
1
[ fanpages ]IT Services ConsultantCommented:
(cough)

Thanks for your help too, Syed :)
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
@aikimark

Point noted.  :)

But see what fanpages said and I echo that.
My point was that it struck me as rude that you did not even acknowledge my proposal, not even a "Thanks, but no thanks".
0
aikimarkCommented:
oops.  No slight, Syed.

==================
@fanpages

good catch.

is a 'cough' the same as a 'harrumph'?
0
Saqib Husain, SyedEngineerCommented:
Hi, all. I was just enjoying recalling the past such experiences of the frustration when your effort goes "unnoticed".

I have had cases where evidently the asker went with my solution but awarded the points to someone else. And I must admit that I am one of those who are "working for points".
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.