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)
As shown in the picture below:
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)
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...
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/