Amazingly Excel STILL doesn't give you the ability to sort a column via IP addresses. You'd think someone in Microsoft would have thought of this by now. well now you can.
Thanks to this, and other posts on the Internet (http://arstechnica.com/civis/viewtopic.php?f=15&t=35273) I've gone one step further and you can now sort your IP address in just one column:
If column A has your IP address range, then column B needs to have this info
I asusme row 1 will have headers, so start your IP range at cell A2 and work downwards.
A2: IP Address
B2: =LEFT(A2,(FIND(".",A2))-1)*256^3+RIGHT(LEFT(A2,(FIND(".",A2,(FIND(".",A2))+1))-1),(FIND(".",A2,(FIND(".",A2))+1))-(FIND(".",A2))-1)*256^2+RIGHT(LEFT(A2,(FIND(".",A2,(FIND(".",A2,(FIND(".",A2))+1))+1))-1),(FIND(".",A2,(FIND(".",A2,(FIND(".",A2))+1))+1))-(FIND(".",A2,(FIND(".",A2))+1))-1)*256+RIGHT(A2,LEN(A2)-(FIND(".",A2,(FIND(".",A2,(FIND(".",A2))+1))+1)))
Now sort by column B and presto!! See attachment is that's all too hard...
IP-Address-Sorter.xls
...