RaqForum 41 No.
298 View  • 
     ...

Split IP addresses and then group rows

 

An Excel table contains a column of standard IP v4 addresses:


 A
1 10.12.20.30
2 10.205.20.30
3 10.178.20.30
4 10.23.20.30
5 10.167.20.30
6 10.90.20.30
7 10.134.20.30
8 10.177.20.30
9 10.200.20.30
10 10.115.20.30
11 10.48.20.30
12 10.181.20.30
13 10.224.20.30
14 10.57.20.30
15 10.180.20.30
16 10.113.20.30
17 10.246.20.30
18 10.179.20.30

Task: Divide IPs into 4 groups evenly according to its second section values – 0-63, 64-127, 128-191 and 192-256, and write the result to 4 columns, as the following shows:


C D E F
1 10.12.20.30 10.90.20.30 10.178.20.30 10.205.20.30
2 10.23.20.30 10.115.20.30 10.167.20.30 10.200.20.30
3 10.48.20.30 10.113.20.30 10.134.20.30 10.224.20.30
4 10.57.20.30 10.177.20.30 10.246.20.30
5

10.181.20.30
6

10.180.20.30
7

10.179.20.30

Type in the following formula in SPL XLL:

=spl("=E@p(E@1(?).group(int(~.split($[.])(2))\64))",A1:A18)

Picture1png

E@p performs row-to-column transposition on the table. E@1 converts the table to a one-dimensional one. group() function groups rows; ~ is the current member, and \ performs the division and gets the integer part.

Source:https://www.reddit.com/r/excel/comments/1cdidn1/sort_list_of_ip_addresses_into_one_of_4_columns/


 
 •  298 View  •