Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old 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
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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to sort names in alphabetical ascending order? Eric Excel Discussion (Misc queries) 7 April 18th 10 09:22 PM
How to sort worksheets in alphabetical order (descending or ascending)? [email protected] Excel Discussion (Misc queries) 2 June 14th 07 10:13 PM
How do I arrange/sort worksheets in ascending order? PurpleRain Excel Discussion (Misc queries) 3 October 20th 06 06:41 AM
Excel worksheets needs to sort ascending or descending order. Md. Mahfuzul Mannan Excel Worksheet Functions 1 September 3rd 06 05:04 PM
Sort Data Into Numerical Order..! Scooby Excel Discussion (Misc queries) 3 July 5th 06 01:24 PM


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

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017