Replace sequential numbers with a range

Question

How can I find sequential numbers in a cell, and replace them with a range?
For example:

change:

1，3，5，15，16，17，25，28，29，31...

to:

1,3,5,15-17,25,28-29,31...

The numbers are already sorted, i.e. in increasing order.

Thanks.

Answer 1

An interesting question that I wanted to look at do without looping through a sequence (which would need sorting first) checking for sequential builds

This function

1. forces the string to a range address
2. uses Union to group consecutive rows together
3. manipulates the string to remove the column identifier

loop wasn't necessary, shorter version!

Function NumOut(strIn As String) As String
Dim rng1 As Range  
Set rng1 = Range("A" & Join(Split(Application.Trim([a1]), ", "), ",A"))
'force the range into areas rather than cells
Set rng1 = Union(rng1, rng1)
NumOut = Replace(Replace(Replace(rng1.Address, "$A$", vbNullstring), ": ", "-"), ",", ", ")
End Function

Answer 2

Thought I'd try an all-formulae solution using Microsoft365's LET() as a way to capture variables.

The below solution only counts 3+ consecutive numbers as ranges of numbers, not two.

Formula in B1:

=LET(X,FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s"),Y,TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[preceding::*[1]+1=.][following::*[1]-1=.]")),SUBSTITUTE(TEXTJOIN(",",,FILTERXML("<t><s>"&TEXTJOIN("</s><s>",,IF(MMULT(--(X=Y),SEQUENCE(COUNTA(Y),,,0)),"-",X))&"</s></t>","//s[.*0=0 or (.='-' and preceding::*[1]*0=0)]")),",-,","-"))