Creating custom number formats

[cadfael]

Greetings all,

I want to sort lists of IP addresses, which Excel normally treats as
strings.

IP addresses are 32-bit binary numbers usually represented as
8-character hexidecimal strings, from 00000000 to FFFFFFFF. For
brevity and to make them easier to grasp they are usually written in
"dotted quad" notation, where the binary or hexidecimal string is split
into four "quads" separated by decimal points. So, the IP address for
www.excelforum.com is '66.98.166.8' but in reality this number is
understood by your computer to be '4262A608' in hex, or 1113761288 in
decimal (if you want to experiment, try entering 'http://1113761288'
into your browser and you'll see that you can still pull up the site).

Excel tries to sort dotted-quad addresses as strings and therefore does
not sort them properly--it will place the IP address 1.1.2.1 AFTER
1.2.21.1 if you try to sort them in ascending order.

Typically when I have to sort large tables of IP addresses, I just
include a row for the "straight decimal" form of the address. I'd like
to do away with this if possible and have Excel read the
straight-decimal format but display the dotted-quad format. Does
anyone know how I can accomplish this?

Thanks in advance,

Pete

 

[Ron Rosenfeld]

Greetings all,

I want to sort lists of IP addresses, which Excel normally treats as
strings.

IP addresses are 32-bit binary numbers usually represented as
8-character hexidecimal strings, from 00000000 to FFFFFFFF. For
brevity and to make them easier to grasp they are usually written in
"dotted quad" notation, where the binary or hexidecimal string is split
into four "quads" separated by decimal points. So, the IP address for
www.excelforum.com is '66.98.166.8' but in reality this number is
understood by your computer to be '4262A608' in hex, or 1113761288 in
decimal (if you want to experiment, try entering 'http://1113761288'
into your browser and you'll see that you can still pull up the site).

Excel tries to sort dotted-quad addresses as strings and therefore does
not sort them properly--it will place the IP address 1.1.2.1 AFTER
1.2.21.1 if you try to sort them in ascending order.

Typically when I have to sort large tables of IP addresses, I just
include a row for the "straight decimal" form of the address. I'd like
to do away with this if possible and have Excel read the
straight-decimal format but display the dotted-quad format. Does
anyone know how I can accomplish this?

Thanks in advance,

Pete

I do not believe you can do what you want by formatting.

How about entering the IP addresses in the dotted-quad format, and using a VB
routine to sort them in place, properly?

Something like the following might work. As written, it sorts a "Selection"
and also requires a reference to the Analysis Tool Pack for the Hex2Dec
function. Although you could write your own if you didn't want that reference.

================================
Option Explicit
Sub sortIP() 'sorts IP addresses
Dim i As Long, j As Long
Dim IP
Dim rg()

ReDim rg(Selection.Count - 1, 1)

For i = 0 To UBound(rg)
rg(i, 0) = Selection.Cells(i + 1, 1).Text
IP = Split(rg(i, 0), ".")
For j = 0 To 3
rg(i, 1) = rg(i, 1) & Right("0" & Hex(IP(j)), 2)
Next j
rg(i, 1) = hex2dec(rg(i, 1))
Next i

rg = BubbleSort(rg, 1)

For i = 0 To UBound(rg)
Selection.Cells(i + 1, 1) = rg(i, 0)
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