Farhan Sabir's Blog

Microsoft Excel sorts IP Addresses in the form of

1.1.x.x., 1.24.x.x., 1.100.x.x, 100.1.x.x, 199.1.x.x, 2.1.x.x, 2.102.x.x, 2.252.x.x, 200.1.x.x, 3.1.x.x ….

and so on. Which makes life difficult when it comes to IP Addresses.

So, the easier way is to Convert the IPs to their Decimal values. Sort them, and then save/use them.

1. Open the IP Addresses file in Excel (which has one IP Address per line in first colomn)
2. Copy that Column 1 to Column 2.
3. Select Column 2
4. Go to DATA and Select Text-To-Columns
5. First Screen shows Delimited. Let it be Like that. Click Next
6. On this window, Tab check box is already Checked. Click on Other and enter “.” in textbox
7. Click Next
8. Click Finish

1.250.x.x	1	250	x	x	33178558
2.55.x.x	2	55	x	x	37181491
101.13.x.x	101	13	x	x	1695352094
200.187.x.x	200	187	x	x	3367717099
222.236.x.x	222	236	x	x	3740048274

Now you have IP Address in first column, First octet in second column, Second octet in 3rd column, 3rd in 4th, and 4th in 5th column. That’s, IP Address and IP Address divided into four octets.

Next step, go to column 6 (Column F) and apply the formula:

=B1*256^3 + C1*256^2 + D1*256^1 + E1*256^0

This will give you a number. For example if IP is 91.229.236.31 the number is 1541794847.

Now, the Last Step: SORT all the data on the last column (Column F in this case).

Now you do not need the columns that we have created. You can delete Column B to Column F.

You have the perfectly sorted IP Addresses.