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

Question

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 |
+-------+-----------+----------------------+

you would need to write a VBA function to do this: you cannot do this using the built-in worksheet functions. — Tim Williams, Jun 21 '16 at 06:14
With Office 365 (actual version) or Office online you can use [TEXTJOIN](https://support.office.com/en-US/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c) like: `{=TEXTJOIN(",",0,ROW(INDEX(A:A,LEFT(A1,FIND("-",A1)-1)):INDEX(A:A,MID(A1,FIND("-",A1)+1,999))))}` — Dirk Reichel, Jun 21 '16 at 06:53

Tim Williams · Accepted Answer · 2016-06-21T23:09:05.517

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

Clever declaration of `sep` after the first iteration to avoid placing a comma at the start :) — RGA, Jun 21 '16 at 07:35
This works perfectly. Now I'm wondering if it's possible to expand on this further and have multiple ranges? ie, "1-3,5-7,9-10" converting to "1,2,3,5,6,7,9,10". — Redlaw, Jun 21 '16 at 23:00
Yes you'd first split on the comma and then process each element of that array: if the element has a "-" then process that as above. — Tim Williams, Jun 21 '16 at 23:03

score 1 · Answer 2 · answered Jun 21 '16 at 06:23

1

Another UDF approach

=Spliced(A1) in B1

Function Spliced(strIn As String) As String
X = Split(strIn, "-")
Spliced = Join(Application.Transpose(Evaluate("=ROW(A" & X(0) & ":A" & X(1) & ")")), ",")
End Function

answered Jun 21 '16 at 06:23

brettdj

54,857
16
114
177

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

2 Answers2

Linked