Sorting by IP Subnets

  • Thread starter Thread starter JohnV
  • Start date Start date
J

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???
 
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???
Click to expand...

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
 
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
 
Thanks to everybody for their response especially Dave. That Dave McRitchie
link was the best solution for me.
 
Back
Top