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
Top comments (1)
Reach out if you have any Excel difficulties. We are happy to help:
🙌🏻Discord
🙌🏻Reddit