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 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 is 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