excel - 将单元格中的值范围转换为逗号分隔列表

Question

我可以在单元格 B1 中使用一个公式来查看 A1 并创建一个基于逗号的列表吗？

下面，A1 是我可以输入的内容。 B1是一个公式。这可能吗？我会让 A1 始终遵循与 XXX-XXX 范围相同的格式。

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

Answer 1

将下面的代码放入常规 VBA 模块中，然后您可以使用(例如):

=NumRange(A1)

在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

编辑 - 处理多个范围

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