On Mon, 1 May 2006 12:46:02 -0700, Kris <
[email protected]> wrote:
>I'm trying to sort IP addresses in Excel, but I can't seem to either adjust
>(custom format) the numbers to end in three digits to get it to sort properly.
>
>For example, if I sort as-is, I get:
>
>192.103.179.1
>192.103.179.10
>192.103.179.100
>192.103.179.11
>192.103.179.110
>192.103.179.12
>
>What I'd like to get is:
>
>192.103.179.1 (or .001)
>192.103.179.10 (or .010)
>192.103.179.11 (or .011)
>192.103.179.12 (or .012)
>192.103.179.100
>192.103.179.110
>
>Any suggestions?
>
>
>
Try the macro below. It should sort the IP addresses if they are in a vertical
array.
To enter it, <alt><F11> opens the VB Editor.
Ensure your project is selected in the project explorer window, then
Insert/Module and paste the code below into the window that opens..
To use it, select either a single cell in the range, or a contiguous range of
cells you wish to sort. Then <alt><F8> opens the Macro Dialog box. Select
SortIP and RUN.
I will be away for a few weeks so hopefully this will work for you without
further intervention
).
=============================================
Option Explicit
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long, k As Long
Dim IP
Dim rg()
Dim RangeToSort As Range
Dim IPaddress As String
Dim IPColumn As Long
IPaddress = "#*.#*.#*.#*"
Set RangeToSort = Selection
'If just one cell selected, then expand to current region
If RangeToSort.Count = 1 Then
Set RangeToSort = RangeToSort.CurrentRegion
End If
'Check if row 1 contains an IP address. If not, it is a header row
'first find column with IP addresses. Check row 2 since row 1 might be a
header
IPColumn = 1
Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
If IPColumn > RangeToSort.Columns.Count Then
MsgBox ("No valid IP address found in Row 1 or Row 2")
Exit Sub
End If
IPColumn = IPColumn + 1
Loop
If Not RangeToSort(1, IPColumn).Text Like IPaddress Then
Set RangeToSort = RangeToSort.Offset(1, 0). _
Resize(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
End If
'one extra column for the IP sort order
ReDim rg(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
rg(i, k) = RangeToSort.Cells(i + 1, k).Text
Next k
IP = Split(rg(i, IPColumn), ".")
For j = 0 To 3
rg(i, 0) = rg(i, 0) & Right("000" & IP(j), 3)
Next j
Next i
rg = BubbleSort(rg, 0)
For i = 0 To UBound(rg)
For k = 1 To UBound(rg, 2)
RangeToSort.Cells(i + 1, k) = rg(i, k)
Next k
Next i
End Sub
'-------------------------------------------
Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
Dim temp() As Variant
Dim i As Integer, j As Integer, k As Integer
Dim NoExchanges As Boolean
k = UBound(TempArray, 2)
ReDim temp(0, k)
Do
NoExchanges = True
For i = 0 To UBound(TempArray) - 1
If TempArray(i, d) > TempArray(i + 1, d) Then
NoExchanges = False
For j = 0 To k
temp(0, j) = TempArray(i, j)
TempArray(i, j) = TempArray(i + 1, j)
TempArray(i + 1, j) = temp(0, j)
Next j
End If
Next i
Loop While Not NoExchanges
BubbleSort = TempArray
End Function
===================================================
--ron
6 (shift left).