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.

27 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
  12. I remember the words: I am the master of my fate, the captain of my soul. What does Henry David Thoreau want me to understand: I captain my soul and I master my fate?ip stresser

    ReplyDelete
  13. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also https://192-168-i-i.com

    ReplyDelete
  14. I'm really impressed about the info you provide in your articles. If you want to invest in suits and be stress-free, then you should shake hands with us. We are providing great clothing in the most reasonable price range that you wouldn’t find anywhere else. Put your trust in us as your supplier since we are the prominent custom stickers printing You can rely on us for the clothing problems we assure you we will never let our customers down.

    ReplyDelete
  15. I visit your blog regularly and recommend it to all of those who wanted to enhance their knowledge with ease. The style of writing is excellent and also the content is top-notch. Thanks for that shrewdness you provide the readers! https://192-168-i-i.com/

    ReplyDelete
  16. Attractive information on your blog, thank you for taking the time and share with us.
    Die Cut Bookmarks

    ReplyDelete
  17. Wow, excellent post. I'd like to draft like this too - taking time and real hard work to make a great article. This post has encouraged me to write some posts that I am going to write soon
    Reflective Stickers
    custom stickers uk
    Custom Box Printing
    Custom Packaging Boxes
    Custom Printed Boxes Uk
    Logo Stickers

    ReplyDelete
  18. Hello, I want to subscribe for this web site to take most up-to-date updates, thus where can i do it please help out.
    custom wig pillow boxes
    custom hair extension boxes
    custom burger boxes

    ReplyDelete
  19. Present your products in the best quality custom printed boxes. Custom boxes make it easy to design and create beautiful custom printed boxes UK. Are you looking for Buy Packaging Boxes with Custom Packaging Boxes at wholesale prices? We offer a huge variety of custom packaging boxes at your doorstep with free shipping.
    Custom Boxes

    ReplyDelete
  20. StickerZone is the fastest and easiest way to buy custom sticker printing. Order in 60 seconds and we’ll turn your designs and illustrations into custom stickers UK, custom labels, window, magnets, buttons and packaging in days. We offer free online proofs, free worldwide shipping and super fast turnaround. We don’t just custom stickers printing. StickerZone provide a wide variety of custom signage for all business types.
    Custom Sticker Printing

    ReplyDelete
  21. I am certainly making the most of your site. You unquestionably have some extraordinary knowledge and incredible stories.
    embossed stickers

    ReplyDelete
  22. Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future also
    Transparent stickers

    ReplyDelete
  23. I always appreciate quality content, Thanks to you for sharing such post.
    Ask Questions Get Answers

    ReplyDelete
  24. Hi! Great work. I feel nice to be here reading your authentic work.
    Custom Sticker


    ReplyDelete
  25. Fine information, many thanks to the author. It is puzzling to me now, but in general, the usefulness and importance is overwhelming. Very much thanks again and best of luck!
    Custom Packaging Boxes
    new birthday wishes

    ReplyDelete