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

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
192.168.1.20 twenty
192.168.1.1 one
when i filter this table
it
replacing like this;
192.168.1.1 twenty
192.168.1.20 one
Thanks for this! - big help
I have a table in sheet named Database and looks like this:
Column A has IP addresses other columns has other data relavant
to each of this IP addresses
row A is header.
Example:
IP Address Data1 Data2 Data3 Data(n)
11.11.11.11 Computer IT Branch1
Question is: How to sort whole table by IPs (column A)?
That means that other columns data should move the whole raw with
IP address to the right position.
The lenght of raw is dynamic as different data is added to the raw
of each IP.
Perfect Solution I am looking for!!
Thanks a ton!!
I notice that if blank cells are selected as part of the range before this macro executes, they are still going through the process and ending up as 0.0.0.0 How/where in the macro could I edit to leave those cells blank, or to not evaluate them if they are blank?
This works well, but a better example would include the ability to sort if the prefixes had a cidr notation as well. e.g. 10.1.1.0/24 & 10.1.2.0/24
Can you do that?
Post a Comment