Sort TCP/IP Address or Delimited Numbers

Are you a reporting analyst in IT firm, network engineer, networking analyst or any one who works on network data? If yes, then you ever tried to sort TCP/IP Addresses, I'm sure something like the red list in this figure, would have happened and you must have desired the green list. This is the issue of sorting with delimited numbers like 1.1.2,  1.1.1,  1.1.10 which results in 1.1.1,  1.1.10,  1.1.2.

Generally, computer and languages on which computers operate treat them as string literals and sorting are based on their ASCII character number. So, the solution is to normalize them, sort them and denormalize back in their original form.


This is how the given VBA macro works. Hope, you understand it easily and leave any questions for clarifications in the comments section.

---

Sub TCP_IP_Sort()
 
 Dim totalcells As Long, ix As Long 
 Dim p1 As Byte, p2 As Byte, p3 As Byte, px As Byte 
 Dim i1 As Byte, i2 As Byte, i3 As Byte, i4 As Byte 
 
 
 Application.ScreenUpdating = False 
 Application.Calculation = xlCalculationManual 
 Application.DisplayAlerts = False
 
 
 On Error Resume Next
 
 
'Part 01 - Converts 127.0.0.1 to 127.000.000.001 form
 
 totalcells = Selection.count 
 For ix = 1 To totalcells  
  actualValue = Selection.Item(ix)  
  p1 = 0  
  p2 = 0  
  p3 = 0  
  For px = 2 To Len(actualValue)   
   If Mid(actualValue, px, 1) = "." Then    
    If p1 = 0 Then     
     p1 = px     
    ElseIf p2 = 0 Then 
                                        p2 = px     
    ElseIf p3 = 0 Then
                                        p3 = px     
    End If    
   End If
  Next px
   
  Selection.Item(ix).Value = Right("00000" & Mid(actualValue, 1, p1 - 1), 3) _
   
   & "." & Right("00000" & Mid(actualValue, p1 + 1, p2 - p1 - 1), 3) _
   
   & "." & Right("00000" & Mid(actualValue, p2 + 1, p3 - p2 - 1), 3) _
   
   & "." & Right("00000" & Mid(actualValue, p3 + 1), 3)
   
 Next ix
   
   
   
   
'Part 02 - Sort the normalized form.
   
 Dim xlWbk As Workbook, xlSht As Worksheet, cRng As String
   
 Set xlWbk = ActiveWorkbook   
 Set xlSht = ActiveSheet 
  
 cRng = Selection.Cells.Address(0, 0)   
 xlSht.Sort.SortFields.Clear   
 xlSht.Sort.SortFields.Add Key:=Range(cRng), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal   
 With xlSht.Sort    
  .SetRange Range(cRng)    
  .Header = xlGuess    
  .MatchCase = False    
  .Orientation = xlTopToBottom    
  .SortMethod = xlPinYin    
  .Apply    
 End With
   
   
   
   
'Part 03 - Convert 127.000.000.001 (normalized form) to 127.0.0.1 (actual form)
   
 For ix = 1 To totalcells    
  actualValue = Selection.Item(ix)    
  p1 = 0    
  p2 = 0    
  p3 = 0    
  For px = 2 To Len(actualValue)     
   If Mid(actualValue, px, 1) = "." Then      
    If p1 = 0 Then       
     p1 = px       
    ElseIf p2 = 0 Then
     p2 = px  
    ElseIf p3 = 0 Then 
     p3 = px
       
    End If      
   End If     
  Next px     
  i1 = Mid(actualValue, 1, p1 - 1) + 0     
  i2 = Mid(actualValue, p1 + 1, p2 - p1 - 1) + 0     
  i3 = Mid(actualValue, p2 + 1, p3 - p2 - 1) + 0     
  i4 = Mid(actualValue, p3 + 1, Len(actualValue) - p3) + 0     
  Selection.Item(ix).Value = i1 & "." & i2 & "." & i3 & "." & i4     
 Next ix

 Application.ScreenUpdating = True     
 Application.Calculation = xlCalculationAutomatic     
 Application.DisplayAlerts = True     
 On Error GoTo 0
     
End Sub