DEV Community

Cover image for #25 — Write Continuous Numbers in A String of Numbers as Intervals
Judith-Excel-Sharing
Judith-Excel-Sharing

Posted on

#25 — Write Continuous Numbers in A String of Numbers as Intervals

Problem description & analysis:
In an Excel worksheet, a cell contains a string made up of an ordered integer sequence. There are missing numbers, and the sequence is not continuous:

1,2,3,4,5,6,7,8,9,13,14,15,16,17,18,25,26,27,28,29,30,31,32

We want to find all continuous sub-sequences, write them in the form of intervals using the hyphen, and separate intervals with the comma.

1–9,13–18,25–32

Solution:
Use SPL XLL to do this:

=spl("=?.split@pc().group@i(~!=~[-1]+1).(if(~.len()==1,~1,~1 / $[-] / ~.m(-1))).concat@c()",A1)
Enter fullscreen mode Exit fullscreen mode

As shown in the picture below:

result table with code entered
Explanation:
split@pc splits a string into a sequence, during which the function automatically parses the data type and uses a comma as the separator. group@i creates a new group whenever the specified condition is met; ~[-1] represents the directly previous members of the current one, and m(-1) is the last member of a sequence.

Top comments (2)

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Please free to download it and explore the data processing journey on your own⬇️
SPL download address: scudata.com/download-Desktop
Plugin Installation Method: c.scudata.com/article/1652061135502
References to other rich Excel operation cases: c.raqsoft.com/article/1651916536524
SPL Programming (YouTube FREE courses): youtube.com/playlist?list=PLQeR-Ih...

Collapse
 
judith677 profile image
Judith-Excel-Sharing

Want your Excel tasks to be analyzed and illustrated in our Excel Hack Series? Please feel free to contact us in our Discord or Reddit communities. We are happy to tackle more Excel difficulties!
☑️Discord: discord.gg/hgbKEvJ4
☑️Reddit: reddit.com/r/esProc_Desktop/