How do I sort a range of IP addresses typed in Excel?

[Amit_23]

Excel does not seem to sort the range of IP addresses successfully. Just sorts 2 levels deep. I use Excel 2000.

[Roger Faucher]

Select the range by holding down the left mouse button on one end of the range, dragging to the other end and releasing the mouse button. The range should now be 'highlighted'. Select Data/Sort and select the appropriate dialog box selections, then click OK. If you don't like the results, click on UNDO (Edit/Undo Sort).

Hope this helps.

Roger

[Ron Kinner]

I have Excel 2000 too but I think it sorts all levels but the sort order is not what you would like. ie it puts the .235 before the .3.

The following website has a fix:

http://www.mrexcel.com/td0027.html

as does:

http://www.i386.com/default.asp?c=6&k=13&p=1046

Both of these assume you know how to use Visual Basic which I don't. The second one does offer a sample spread sheet which should make it easier to figure out.

I did find that you can't just copy and paste the formula into the module. It comes out as one long line of text. Works better if you paste it to Word first then copy it off Word and paste it to your VB module.

There is another way. It's a bit clumsy but it works without VB assuming you do not have any other "."'s in the file. Save your spreadsheet as File Type: text (tab delimited) .txt then open the .txt file in Excel. It should start the Text Import Wizzard which will say the file is delimited. Click Next. Under Delimiters leave Tab checked and also check Other and put a "." in the box. Now hit Next and Finish. This will bring up your spreadsheet but now it will have the IP address spread over 4 columns. Now sort based on the 4th column in the IP address. Then sort again this time on the first three columns which make up the IP address. It should now be sorted the way you like it.

If you want it to still look like a standard IP address you can format/cells/number/custom each of the first three columns to have a custom type of "###." which will add a "." to the end of the each number in the first three columns and then you can play with the column widths to squeeze them back together.

Ron

[Roger Faucher]

Ron's right. Upon closer examination, my method doesn't work. Sorry.

I have discovered a somewhat tedious way to do it, however. Assume you have a column of cells containing IP addresses (period-delimited). Highlight the range, select Data/Text to Columns. In Step 1, choose Delimited, in Step 2, select Other and insert a period in the adjacent box. In Step 3, change the destination field to point to where you want the IP address broken in sections (one section per column). NextNext, you'll need to create a custom sort order. In the MS Excel Help Answer Wizard, search on "Create a custom fill series or sorting order" Using that procedure, create a custom sorting order consisting of the numbers 0 through 255. (I have attached a text file which you can cut and paste to save you the tedium of entering the data). The next step is to select the rightmost column, select Data/Sort Expand the Selection, click Sort, click the Options button and under 'First Key Sort Order', select the custom sort order created earlier. Then under SORT BY, select the rightmost column, ascending, and click OK. Repeat the sort (without changing the cells selected) for each column (one column at a time, rightmost to leftmost).

This worked for me using Excel 2000. There is one potential problem. When I went into the custom sort order, it had truncated the last two numbers (254 and 255). However, I don't think this will effect you. I'm pretty sure it would work if you only entered 0 through 100 in the sort order. I also extracted the four pieces of the IP addresses into the adjacent 4 columns, which conveniently sorted the original IP address column as a function of performing the steps outlined above. I hope this will be useful.

Good luck!

Roger