Complex Formula / VBA UDF Perhaps?

[CROY1985]

Hi all

I would like to convert a string which would contain a mix of numbers and ranges (of numbers) into a complete list of integers.

The format would for example be:
1,3-4,6

and I want to convert this to a text string which would be:
1,3,4,6

A second example would be:
4,8-14,15,17-20

which i'd want to convert to:
4,8,9,10,11,12,13,14,15,17,18,19,20

Any pointers much appreciated.

Thanks

 

[YasserKhalil]

Try this

Sub Test_UDF()
    Debug.Print PopulateNumbers("4,8-14,15,17-20")
End Sub

Function PopulateNumbers(s As String)
    Dim x, y, out As String, i As Long, j As Long, m As Long, n As Long
    
    x = Split(s, ",")
    
    For i = LBound(x) To UBound(x)
        If InStr(x(i), "-") Then
            y = Split(x(i), "-")
            m = Application.Min(y(0), y(1))
            n = Application.Max(y(0), y(1))
            
            For j = m To n
                out = out & IIf(out <> "", ",", "") & j
            Next j
        Else
            out = out & IIf(out <> "", ",", "") & x(i)
        End If
    Next i
    
    PopulateNumbers = out
End Function

 

[Peter_SSs]

Here is my UDF

Function ListNums(s As String) As String
  Dim sp1 As Variant, sp2 As Variant
  Dim i As Long
 
  For Each sp1 In Split(s, ",")
    sp2 = Split(sp1, "-")
    For i = sp2(0) To sp2(UBound(sp2))
      ListNums = ListNums & "," & i
    Next i
  Next sp1
  ListNums = Mid(ListNums, 2)
End Function

Book1
	A	B
1	1,3-4,6	1,3,4,6
2	4,8-14,15,17-20	4,8,9,10,11,12,13,14,15,17,18,19,20
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=ListNums(A1)

 

[lrobbo314]

This looked like a fun one to try. Here's my version for kicks.

Function SEQUENCE(s As String)
Dim SP() As String: SP = Split(s, ",")
Dim SP2() As String
Dim AR As Variant

For i = 0 To UBound(SP)
    If InStr(SP(i), "-") > 0 Then
        SP2 = Split(SP(i), "-")
        AR = Evaluate("Transpose(Row(" & SP2(0) & ":" & SP2(1) & "))")
        SP(i) = Join(SP2, ",")
    End If
Next i

SEQUENCE = Join(SP, ",")
End Function

 

[YasserKhalil]

@lrobbo314

This line

SP(i) = Join(SP2, ",")

Should be

SP(i) = Join(AR, ",")

 

[Rick Rothstein]

Here is another function that you can try...

Function Sequence(ByVal S As String) As String
  Dim X As Long, V As Variant
  For Each V In Split(Replace(S, "-", ":"), ",")
    For X = Evaluate("MIN(" & Replace(S, "-", ",") & ")") To Evaluate("MAX(" & Replace(S, "-", ",") & ")")
      If Not Intersect(Rows(V), Rows(X)) Is Nothing Then Sequence = Sequence & "," & X
    Next
  Next
  Sequence = Mid(Sequence, 2)
End Function

 

[lrobbo314]

@lrobbo314

This line

SP(i) = Join(SP2, ",")

Should be

SP(i) = Join(AR, ",")

Right you are sir, thanks for pointing that out. I had it split between 2 functions and just mixed that one up reducing it to 1 function.