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👇

SPL download address: esProc Desktop FREE Download

Plugin Installation Method: SPL XLL Installation and Configuration

References to other rich Excel operation cases: Desktop and Excel Data Processing Cases

YouTube FREE courses: SPL Programming