#59 - Split IP Addresses And Then Group Rows

September 6, 2024

This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing

Problem description & analysis:

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

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

Solution:

Type in the following formula in SPL XLL:

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

Explanation:

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.

The example was originally on Reddit.
It’s all about making your spreadsheets work for you, not against you! Who’s ready to level up their data game? Check those links below