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.
Source:https://www.reddit.com/r/excel/comments/1cdidn1/sort_list_of_ip_addresses_into_one_of_4_columns/
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_Desktop/
SPL Learning Material 👉 https://c.scudata.com
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProcDesktop
Linkedin Group 👉 https://www.linkedin.com/groups/14419406/
