Thursday, February 5, 2009

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):

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

  8. 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.

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

  10. 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.

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

11 comments:

  1. Thanks a million, this works perfectly and I don't have to deal with Macros or VB.
    Thanks again.

    ReplyDelete
  2. It's really cool & simple. No need to worry about VBA, scripts etc.

    Thanks a lot.

    Regards...
    -Ashok.

    ReplyDelete
  3. Hi,

    I have found a bug in this procedure.

    Initially, we sort first three columns which is proper. But, we need to sort 4th column separately & final concatenation is resulting the earlier subnets with incorrect 4th column value or the last octet.

    Regards...
    -Ashok.

    ReplyDelete
  4. great solution -- sorts like a charm

    ReplyDelete
  5. [...] http://blog.zztopping.com/2009/02/05/how-to-sort-ip-addresses-in-microsoft-excel/ [...]

    ReplyDelete
  6. Also you can use a formula. For example, if the ip address is on A1 celd, you can use folowing and then paste and copy where is necesary:
    =TEXT(LEFT(A1,FIND(".",A1,1)-1),"000")&"."&TEXT(TRUNC(MID(A1,FIND(".",A1,1)+1,3)),"000")&"."&TEXT(TRUNC(MID(A1,FIND(".",A1,FIND(".",A1,1)+1)+1,3)),"000")&"."&TEXT(TRUNC(MID(A1,FIND(".",A1,FIND(".",A1,FIND(".",A1,1)+1)+1)+1,3)),"000")

    Also you can use a simple VBA. You can find how at:
    http://itech123.com/viewtopic.php?f=2&t=2

    ReplyDelete
  7. Good stuff. Very useful if you do this very often.

    ReplyDelete
  8. This comment has been removed by a blog administrator.

    ReplyDelete
  9. This comment has been removed by the author.

    ReplyDelete

  10. I have not heard about before. Keep sharing more blog posts on this Community and get extra benefits with this ideas and knowledge. Thanks for this one.
    sms-api
    gatewayprovider
    transactional

    ReplyDelete
  11. Great post! Thank you for sharing valuable information. Keep up the good work.microsoft courses malaysia

    ReplyDelete