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.

53 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. [...] http://blog.zztopping.com/2009/02/05/how-to-sort-ip-addresses-in-microsoft-excel/ [...]

    ReplyDelete
  5. 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
  6. Good stuff. Very useful if you do this very often.

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

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

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

    ReplyDelete
  10. 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
  11. 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
  12. 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
  13. Attractive information on your blog, thank you for taking the time and share with us.
    Die Cut Bookmarks

    ReplyDelete
  14. 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
  15. 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
  16. 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
  17. I am certainly making the most of your site. You unquestionably have some extraordinary knowledge and incredible stories.
    embossed stickers

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


    ReplyDelete
  19. 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
  20. 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
  21. 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
  22. 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
  23. 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
  24. 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
  25. 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
  26. 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
  27. 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
  28. 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
  29. 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
  30. This comment has been removed by the author.

    ReplyDelete
  31. 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
  32. This comment has been removed by the author.

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

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

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

    ReplyDelete
  36. After a long time, I read a very beautiful and very important article that I enjoyed reading. I have found that this article has many important points, I sincerely thank the admin of this website for sharing it.

    custom boxes

    ReplyDelete
  37. Very informative post ! There is a lot of information here that can help any business get started with a successful social networking campaign ! I feel a lot more people need to read this, very good info! cupcake boxes | cupcake boxes

    ReplyDelete
  38. Wonderful article, thanks for putting together! This is obviously one great post. Thanks for the valuable information and insights you have so provided here. Microsoft Excel pivot tables training

    ReplyDelete
  39. Positive site, where did u come up the information on this posting?I have read a few of the articles on your website now, and I really like your style. Thanks a million and please keep up the effective work.
    tellthebelltacobell

    ReplyDelete
  40. This is a really well presented post, my compliments. it's tons of key elements that really makes it work. and that I love the outline and wordings. you actually have a magnetic power that catches everyone in to your blog. Good one, and keep it packaging custom boxes.

    ReplyDelete
  41. Yes i am totally agreed with this article and i just want say that this article is very nice and very informative article.I will make sure to be reading your blog more. You made a good point but I can't help but wonder, what about the other side? Get for more information custom rigid box packaging

    ReplyDelete
  42. It is an honest site post without fail. Not too many of us would actually, the way you only did. i'm impressed that there's such a lot information about this subject that has been uncovered and you’ve defeated yourself this point , with such a lot quality. Good Works! custom lip gloss boxes

    ReplyDelete
  43. Really appreciate this wonderful post that you have provided for us. Great site and a great topic as well I really get amazed to read this. It's excellent. Soap Packaging Boxes

    ReplyDelete


  44. The first thing that I must say is that you have a unique writing style. An interactive cum interesting style of explaining things. One thing is for sure that these tips are simply great!
    custom promotional products

    ReplyDelete
  45. The cosmetic boxes UK provide top-quality wholesale custom printed cosmetic packaging boxes UK in all sizes and colors. Based in London, Free Shipping...fresh popcorn box | fresh popcorn box

    ReplyDelete
  46. often to investigate cross-check new posts Very good information. Lucky me I came across your site by chance (stumbleupon). I have bookmarked it for later.printed personal care boxes | printed personal care boxes

    ReplyDelete
  47. You have shared amazing post. This post is really helpful for us to know the information. Thank you for taking your time to post such a wonderful article. Get for more information https://globdaily.com/guest-post/

    ReplyDelete
  48. This is awesome post I ever find that relates with my information .I really like it the most. You have really done great job by sharing the beautiful post. Thanks lot for the informative post. Learn to read more about. thanks for sharing

    soap packaging boxes
    popcorn boxes
    Custom Hair Extension Box
    die cut packaging boxes
    essential oil packaging boxes

    ReplyDelete