Sorting by IP Subnets

[JohnV]

I've looked everywhere but I can't find any information on how, if possible,
to sort by subnets.
ie. If I sort the following data in Excel, I have the following data:
192.168.1.0
192.168.10.0
192.168.8.0

But I want the following to occur,
192.168.1.0
192.168.8.0
192.168.10.0

as the 3rd octet, 8, is less than 10.

Any ideas???

 

[Mike A]

The problem is that the addresses are not numbers. They sort
alphabetically.

You could enter the octets in separate cells, say a1 to d1, and sort
on the right-hand two columns. Then, where you want the dotted-four
format to appear, use the formula:

=CONCATENATE(A1,".",B1,".",C1,".",D1)

Then auto-fill or copy the formula down however many rows you need.

You can hide the columns with the separate octets, if you wish.


-Mike

I've looked everywhere but I can't find any information on how, if possible,
to sort by subnets.
ie. If I sort the following data in Excel, I have the following data:
192.168.1.0
192.168.10.0
192.168.8.0

But I want the following to occur,
192.168.1.0
192.168.8.0
192.168.10.0

as the 3rd octet, 8, is less than 10.

Any ideas???

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers

 

[Dave Peterson]

You could use data|text to columns and split them up into 4 separate columns and
recombine them:

=TEXT(B1,"000")&"."&TEXT(C1,"000")&"."&TEXT(D1,"000")&"."&TEXT(E1,"000")
or even:
=TEXT(B1,"000.")&TEXT(C1,"000.")&TEXT(D1,"000.")&TEXT(E1,"000")

Or you could use some code like David McRitchie did at:
http://www.mvps.org/dmcritchie/excel/sorttcp.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Nikos Yannacopoulos]

John,

Copy the IP column to an empty one and parse (Data > Text to Columns) using
period as a delimiter. Then you can sort on the subnet (now a numeric column
on its own right), while your original column still holds the whole IP.

HTH,
Nikos

 

[JohnV]

Thanks to everybody for their response especially Dave. That Dave McRitchie
link was the best solution for me.

 

[David McRitchie]

Thanks, I certainly try to minimize extra work, and have worked
with lists of TCP/IP addresses. I do remember generating a label
for each end of the cable, wasn't in Excel.

The formula to add 1 to a tcp/ip number by Chip Pearson along
with Mail Merge would make such things easier for Excel users.
http://www.mvps.org/dmcritchie/excel/sorttcp.htm
http://www.mvps.org/dmcritchjie/excel/mailmerg.htm