View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
[email protected] jirving@hornblower.com is offline
external usenet poster
 
Posts: 1
Default How do I sort IP addresses is ascending numerical order?

Amplifying Dana DeLouis' solution...The following method works great. You need 6 columns, but can sort on only one.

Let's say A2 contains the IPv4 address in normal non-zero-filled format like "192.168.15.42". In B2 enter the formula:
=split(A2,".")

which puts the four numeric address terms in columns B2, C2, D2 and E2. Then in F2 enter
=4294967296*B2+65536*C2+256*D2+E2

For example, IP 192.168.15.42 generates an "IP sort" number 824644734762.

Copy the two formulas down their respective columns, call column E "IPsort" or some such and sort ascending on just this column.