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👇
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
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-09-06T04:31:00+00:00) #59 - Split IP Addresses And Then Group Rows. Retrieved from https://www.scien.cx/2024/09/06/59-split-ip-addresses-and-then-group-rows/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.