Sorting IP Addresses in Excel?

[Kris]

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?

[Bob Umlas, Excel MVP]

Select the cells, use Data/Text to Columns, select Delimited in step 1,
select "Other" checkbox and enter a period, then click finish. You can then
sort by collumn D. Then you can put the cells together again with a formula
in E1:
=A1&"."&B1&"."&C1&"."&D1 then fill down, then copy E1:E6 (in this example),
Edit/Paste special values, then delete A1:D6 (shift left).
HTH

"Kris" 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?
>
>
>
>

[Jim Cone]

An alternative is the commercial Excel add-in "Special Sort" from yours truly...
http://www.officeletter.com/blink/specialsort.html
--
Jim Cone
San Francisco, USA



"Kris" <[email protected]>
wrote in message
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?

[Ron Rosenfeld]

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