This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
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.
This content originally appeared on DEV Community and was authored by Judith-Excel-Sharing
Judith-Excel-Sharing | Sciencx (2024-07-15T03:36:42+00:00) #25 — Write Continuous Numbers in A String of Numbers as Intervals. Retrieved from https://www.scien.cx/2024/07/15/25-write-continuous-numbers-in-a-string-of-numbers-as-intervals/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.