Function for Comma-Separated Values within Cell

**SoothSailor** · 11-07-2014, 04:24 PM

I already have a function to take a comma-separated string within a cell and change the values into dashes (I'll attach the .bas file) Here's an example of how it already works:

	A	B	C
1	Comma-Separated Cells	"Numdash" Function	Result
2	1,2,3,4,5	=NUMDASH(A2)	1-5
3	1,2,3,5	=NUMDASH(A3)	1-3,5
4	1,2,3,5,7,8,9,10,11,12	=NUMDASH(A4)	1-3,5,7-12

Now I need a way to reverse-engineer it so it takes the dashed values and shows all the numbers between them, like this:

	A	B	C
1	Dash-Separated Cells	"Numcomma" Function	Result
2	1-5	=NUMCOMMA(A2)	1,2,3,4,5
3	1-3,5	=NUMCOMMA(A3)	1,2,3,5
4	1-3,5,7-12	=NUMCOMMA(A4)	1,2,3,5,7,8,9,10,11,12

Here's a sample of how the macro that I have works;
NUMDASH.xlsm

Option Explicit
Function NUMDASH(txt As String) As String
'UDF to summarize sequential numbers by range using dash (-)
'e.g. 1,2,3,6,7,8 becomes 1-3,6-8 in result
    Dim e, i As Long
    If txt Like "*,*" Then
        With CreateObject("System.Collections.ArrayList")
            For Each e In Split(txt, ",")
                If Val(e) <> 0 Then
                    If Not .Contains(Val(e)) Then .Add Val(e)
                End If
            Next
            If .Count > 1 Then
                .Sort: NUMDASH = .Item(0) & "-"
                For i = 1 To .Count - 1
                    If .Item(i) - .Item(i - 1) > 1 Then
                        NUMDASH = IIf(NUMDASH Like "*-", Left$(NUMDASH, Len(NUMDASH) - 1), NUMDASH) _
                                & "," & .Item(i) & "-"
                    Else
                        NUMDASH = Left$(NUMDASH, InStrRev(NUMDASH, "-") - 1) & "-" & .Item(i)
                    End If
                Next
                NUMDASH = IIf(NUMDASH Like "*-", Left$(NUMDASH, Len(NUMDASH) - 1), NUMDASH)
            Else
                NUMDASH = .Item(0)
            End If
        End With
    Else
        NUMDASH = txt
    End If
End Function

**Bob Phillips** · 11-07-2014, 06:21 PM

Public Function NumComma(ByRef cell As Range) As String
Dim tmp As String
Dim ary As Variant
Dim pos As Long
Dim i As Long, ii As Long

    ary = Split(cell.Value, ",")
    For i = LBound(ary) To UBound(ary)
    
        pos = InStr(ary(i), "-")
        If pos > 0 Then
        
            For ii = Left$(ary(i), pos - 1) To Mid$(ary(i), pos + 1, 99)
            
                tmp = tmp & ii & ","
            Next ii
        Else
            
            tmp = tmp & ary(i) & ","
        End If
    Next i
    
    NumComma = Left$(tmp, Len(tmp) - 1)
End Function