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. |
All times are GMT +1. The time now is 04:08 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com