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.

59 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
  26. I really thank you for the valuable info on this great subject and look forward to more great posts
    Custom Packaging Boxes

    ReplyDelete
  27. Would highly suggest reordering so that the “Post Comment” is the first key stroke after hitting the tab key. As it stands now, not using the mouse, you must hit the tab key six times. Otherwise looks nice and clean. custom kraft boxes

    ReplyDelete
  28. It’s a great pleasure reading your post. Its full of information I am looking for and I love to post a comment that "The content of your post is awesome" Great work. Custom gift boxes

    ReplyDelete
  29. I like the valuable info you provide in your articles. I will bookmark your blog and check again here frequently. I am quite certain I will learn many new stuffs right here! Good luck for the next! custom packaging boxes

    ReplyDelete
  30. Good but i have problem for responsive Google+ comment on my website custom packaging boxes usa

    ReplyDelete
  31. I am impressed. I don't think I've met anyone who knows as much about this subject as you do. You are truly well informed and very intelligent. You wrote something that people could understand and made the subject intriguing for everyone. Really, great blog you have got here
    Custom Counter Display Boxes
    Custom Christmas Boxes
    Printed on Demand Boxes UKCustom Soap Boxes
    Cannabis Tinctures Packaging
    Custom Vape Packaging
    E-Cigarette Boxes
    Printed on Demand Boxes UK

    ReplyDelete
  32. I like the valuable info you provide in your articles. I will bookmark your blog and check again here frequently. I am quite certain I will learn many new stuffs right here! Good luck for the next!
    factory outlets market of clothing brands in Lahore

    ReplyDelete
  33. Our service has been very supportive to our customers and especially in solving their problems and providing them with best boxes which are fully credible and worthy of their trust.
    Packaging for Bath Bombs
    custom boxes
    burger boxes
    french fries boxes

    ReplyDelete
  34. Excellent post. I was checking constantly this blog and I am impressed! Very useful info.Thanks for sharing.Keep it up.


    factory outlets market of clothing brands in Lahore

    ReplyDelete
  35. Excellent post. I was checking constantly this blog and I am impressed! Very useful info.Thanks for sharing.Keep it up.


    Create PayPal account in Pakistan legally

    ReplyDelete
  36. Today, I was just browsing along and came upon your blog. Just wanted to say good blog and this article helped me a lot, due to which I have found exactly I was looking. Custom boxes Printing

    ReplyDelete
  37. This article was written by a real thinking writer. I agree many of the with the solid points made by the writer. I’ll be back. Custom boxes

    ReplyDelete
  38. I have been working for Buy Dissertation Online as a data analyst for almost three years now, you post is really informative as I am going to use it for my office work. Thanks for sharing this great post.

    ReplyDelete
  39. This blog is amazing keep sharing quality custom boxes
    very fast and great service Customized Packing Box Company
    best birthdays wishes i like it new birthday wishes

    ReplyDelete
  40. Today, I was just browsing along and came upon your blog. Just wanted to say good blog and this article helped me a lot, due to which I have found exactly I was looking. Custom boxes Printing

    Custom Packaging Boxes

    ReplyDelete
  41. Promotional Carrier Boxes Same thing here, I chose to go with Discus as well. At least my website users have a choice of several social media to log in and share their thoughts.

    ReplyDelete
  42. We make the full scope of pillow boxes custom in every single custom tone, shapes and sizes. With our most recent printing method, we can help make your exclusively printed pad boxes with logo and support your brand picture on the lookout.
    Soap Boxes
    Custom Toy Box
    Soap Packaging

    ReplyDelete
  43. At the custom packaging boxes we offer a branding opportunity, which is so important in today's competitive 'stand out' market. Brand identity has never been more valuable and here at custom packaging boxes Company we work with you to
    make our products part of your brand. With options such as in-house foil printing at low minimum orders all the way up to fully gift boxes wholesale and wedding gift bags

    ReplyDelete
  44. Quality Packaging Solutions, the name itself is self-evident of our quick and efficient services. We at Quality Packaging Solutions offer a wide variety of customized packaging options to our customers. You can get your Custom E-juice Shipping Boxes at Quality Packaging Solutions in a cost-effective manner.

    ReplyDelete
  45. Quality Packaging Solution makes it simple for businesses to design and order custom boxes and affordable packaging online. You can get yourCustom E-juice Boxes at Quality Packaging Solutions in a cost-effective manner. you can get any quantity of Custom Boxes produced.

    ReplyDelete
  46. Are you looking for the best Bespoke Packaging Boxes? Check out our rectangle gift boxes with Lids selection for the very best in unique or custom. We are serving thousands of businesses in the UK, USA, and Australia.

    ReplyDelete
  47. Are you looking for the best Bespoke Mailer Boxes? Check out our Custom Mailer Boxes UK selection for the very best in unique or custom. We are serving thousands of businesses in the UK with free doorstep delivery.

    ReplyDelete
  48. Heyy!!! awesome post and Nice Journey. I got so involved in this material that I couldn’t stop reading. sms marketing service provider

    ReplyDelete
  49. Microsoft Excel has an extensive feature set for all types of applications, be it accounting, engineering, technology or marketing. Get your Office suite from www.Office.Com/Setup, and manage your business reports efficiently.

    ReplyDelete
  50. Usually i'm not committing to any post but your blog post is forcing me to do it ,your knowledge is very good. Thank you for sharing your knowledge with us. side load case packer

    ReplyDelete
  51. Every business has Packaging needs, and we are specialists in knowing your custom boxes printing needs. We produce Custom Printing Services, Custom Boxes and additionally carefully make them fill your company a twofold need of Custom Printing Services co. UK and furthermore developing your things.Buy Packaging Boxes Buy Packaging Boxes

    ReplyDelete
  52. Wow! Thank you! I constantly wanted to write on my site something like that. Can I take a portion of your post to my website? Best custom boxes service provider.

    ReplyDelete
  53. Thanks for sharing this informative article. For more info about custom foam insert very beautiful article nice use of pics.
    Soap boxes
    bath bomb packaging

    ReplyDelete