# 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)

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.