![如何在 MS Excel 2007 和 2010 中将一系列值扩展为用逗号分隔的单个值](https://linux22.com/image/1465200/%E5%A6%82%E4%BD%95%E5%9C%A8%20MS%20Excel%202007%20%E5%92%8C%202010%20%E4%B8%AD%E5%B0%86%E4%B8%80%E7%B3%BB%E5%88%97%E5%80%BC%E6%89%A9%E5%B1%95%E4%B8%BA%E7%94%A8%E9%80%97%E5%8F%B7%E5%88%86%E9%9A%94%E7%9A%84%E5%8D%95%E4%B8%AA%E5%80%BC.png)
如何将一系列值扩展为用逗号分隔的单个值?
示例 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