Remember Me?

#1
April 13th 16, 12:12 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Apr 2016 Posts: 1
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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Eric Excel Discussion (Misc queries) 7 April 18th 10 09:22 PM [email protected] Excel Discussion (Misc queries) 2 June 14th 07 10:13 PM PurpleRain Excel Discussion (Misc queries) 3 October 20th 06 06:41 AM Md. Mahfuzul Mannan Excel Worksheet Functions 1 September 3rd 06 05:04 PM Scooby Excel Discussion (Misc queries) 3 July 5th 06 01:24 PM

All times are GMT +1. The time now is 05:56 AM.