☰

如何在 MS Excel 2007 和 2010 中将一系列值扩展为用逗号分隔的单个值

tag-icon • tag-icon microsoft-excel-2010 microsoft-excel-2007 range
如何在 MS Excel 2007 和 2010 中将一系列值扩展为用逗号分隔的单个值

如何将一系列值扩展为用逗号分隔的单个值？

示例 1：

将 1-10转换R1-R8为R1, R2, R3, R4, R5, R6, R7, R8
1, 2, 4, 5, 6, 7, 8, 9, 10

我需要将这些扩展范围用逗号分隔并放在一个单元格中。
有没有公式可以做到这一点？

答案1

此 VBA 代码将完成以下工作：

Public Function sequence(data As String)
    result = ""
    endvalues = Split(data, "-")
    minval = endvalues(0)
    maxval = endvalues(1)
    lminval = Len(minval)
    lmaxval = Len(maxval)
    For i = 1 To lminval
        singlechar = Mid(minval, i, 1)
        If IsNumeric(singlechar) = True Then
            minchar = Left(minval, i - 1)
            minnum = Right(minval, lminval - (i - 1))
            i = lminval
        End If
    Next i
    For i = 1 To lmaxval
        singlechar = Mid(maxval, i, 1)
        If IsNumeric(singlechar) = True Then
            maxchar = Left(maxval, i - 1)
            maxnum = Right(maxval, lmaxval - (i - 1))
            i = lminval
        End If
    Next i
    For i = minnum To maxnum
        result = result & minchar & i & ","
    Next i
    result = Left(result, Len(result) - 1)
    sequence = result
End Function

打开宏/VBA，在本工作簿插入一个模块并将其粘贴到窗口的右侧。

如果在手机上A1例如，R1-R5然后B1你可以在单元格中放入=sequence(A1)以获取结果R1、R2、R3、R4、R5

答案2

Function ExpandARangeOfValues2_OK(num As Variant)
Dim numArray
numArray = "1-10"
Dim rangeValues() As String
        rangeValues = Split(numArray, "-")
        Dim startNum As Long
        Dim endNum As Long
        startNum = Val(rangeValues(0))
        endNum = Val(rangeValues(1))
        For num = startNum To endNum
            outputStr = outputStr & num & ", "
        Next num
        'MsgBox outputStr
        ExpandARangeOfValues2_OK = outputStr
End Function

相关内容