How to Sort IP Addresses in Microsoft Excel

A strange combination of Excel tools will yield a method to sort a long list of IP Addresses. I'll start from copying a list from a Cisco Router ARP Table.

Here's how:

1. Paste the ARP Table into Excel, it will be a long line of text all in one column.


2. Select the column by clicking the letter at the top and run the "text to columns" tool.


3. Run through the short wizard using a space deliminator, should yeild you with 5 or so columns, one for IP, one for MAC, and the others.


4. Now, select the new column where the IP Addresses are, and run the same text to columns tool again, this time using a period as the deliminator.(you may want to first insert 3 blank columns to the right of the IP Address to make room for this new columnized data)


5. Now, sort by lowest to highest using first octet, then by second octet, then by third octet, and then by fourth octet.


6. Insert yet another column anywhere, and on line one use the CONCATENATE function as such(a1=first octet of first address, B1 as second of first, etc):



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



7. This should yield a cell with the address formatted properly. Select it and click the dot in the lower right hand corner of the selection and drag all the way down every row in your table, thereby copying the formula with relative cell modifications in your formula.


8. Now, select all these addresses in this column, and copy them.


9. By default, pasting them in another column will only paste formula data, so under Edit->Paste Special(or if you have 2007, click the arrow underneath the paste button) and select Paste Values.


10. Tada! You can now delete all 5 of you intermediary columns.