Write continuous numbers in a string of numbers as intervals

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:


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


Use SPL XLL to do this:

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


split@pc splits a string into a sequence, during which the function automatically parses the data type and uses 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.