Turn a value range in a cell into a comma separated list

Redlaw Published at Dev

Redlaw

Is there a formula I can have in cell B1 that looks at A1 and creates a comma-based list?

So below, A1 is something I can type into. B1 is a formula. Is this possible? I will have A1 always follow the same format as a XXX-XXX range.

+-------+-----------+----------------------+
| TABLE | A (Input) |      B (Result)      |
+-------+-----------+----------------------+
|     1 | 1-10      | 1,2,3,4,5,6,7,8,9,10 |
+-------+-----------+----------------------+

Tim Williams

Put the code below in a regular VBA module then you can use (eg):

=NumRange(A1)

in B1

Function NumRange(v)
    Dim arr, x As Long, rv As String, sep As String
    If InStr(v, "-") Then
        arr = Split(v, "-")
        arr(0) = Trim(arr(0))
        arr(1) = Trim(arr(1))
        If IsNumeric(arr(0)) And IsNumeric(arr(1)) Then
            For x = CLng(arr(0)) To CLng(arr(1))
                rv = rv & sep & x
                sep = ","
            Next x
        End If
    End If
    NumRange = rv
End Function

EDIT - handle multiple ranges

Function NumRange(v)
    Dim arrC, arr, x As Long, rv As String, sep As String, e

    arrC = Split(v, ",")
    rv = ""

    For Each e In arrC
        If InStr(e, "-") Then
            arr = Split(e, "-")
            arr(0) = Trim(arr(0))
            arr(1) = Trim(arr(1))
            If IsNumeric(arr(0)) And IsNumeric(arr(1)) Then
                For x = CLng(arr(0)) To CLng(arr(1))
                    rv = rv & sep & x
                    sep = ","
                Next x
            End If
        ElseIf IsNumeric(e) Then
            rv = rv & sep & CLng(e)
            sep = ","
        End If
    Next e
    NumRange = rv
End Function

Collected from the Internet

Please contact [email protected] to delete if infringement.