software

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?Select the cells, use Data/Text to Columns, select Delimited in step 1,
select quot;Otherquot; 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:
=A1amp;quot;.quot;amp;B1amp;quot;.quot;amp;C1amp;quot;.quot;amp;D1 then fill down, then copy E1:E6 (in this example),
Edit/Paste special values, then delete A16 (shift left).
HTH

quot;Krisquot; wrote:

gt; I'm trying to sort IP addresses in Excel, but I can't seem to either adjust
gt; (custom format) the numbers to end in three digits to get it to sort properly.
gt;
gt; For example, if I sort as-is, I get:
gt;
gt; 192.103.179.1
gt; 192.103.179.10
gt; 192.103.179.100
gt; 192.103.179.11
gt; 192.103.179.110
gt; 192.103.179.12
gt;
gt; What I'd like to get is:
gt;
gt; 192.103.179.1 (or .001)
gt; 192.103.179.10 (or .010)
gt; 192.103.179.11 (or .011)
gt; 192.103.179.12 (or .012)
gt; 192.103.179.100
gt; 192.103.179.110
gt;
gt; Any suggestions?
gt;
gt;
gt;
gt;

An alternative is the commercial Excel add-in quot;Special Sortquot; from yours truly...
www.officeletter.com/blink/specialsort.html
--
Jim Cone
San Francisco, USA
quot;Krisquot; gt;
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?On Mon, 1 May 2006 12:46:02 -0700, Kris gt; wrote:

gt;I'm trying to sort IP addresses in Excel, but I can't seem to either adjust
gt;(custom format) the numbers to end in three digits to get it to sort properly.
gt;
gt;For example, if I sort as-is, I get:
gt;
gt;192.103.179.1
gt;192.103.179.10
gt;192.103.179.100
gt;192.103.179.11
gt;192.103.179.110
gt;192.103.179.12
gt;
gt;What I'd like to get is:
gt;
gt;192.103.179.1 (or .001)
gt;192.103.179.10 (or .010)
gt;192.103.179.11 (or .011)
gt;192.103.179.12 (or .012)
gt;192.103.179.100
gt;192.103.179.110
gt;
gt;Any suggestions?
gt;
gt;
gt;

Try the macro below. It should sort the IP addresses if they are in a vertical
array.

To enter it, lt;altgt;lt;F11gt; 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 lt;altgt;lt;F8gt; 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 = quot;#*.#*.#*.#*quot;

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 gt; RangeToSort.Columns.Count Then
MsgBox (quot;No valid IP address found in Row 1 or Row 2quot;)
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), quot;.quot;)
For j = 0 To 3
rg(i, 0) = rg(i, 0) amp; Right(quot;000quot; amp; 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) gt; 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