将单元格中的值范围转换为逗号分隔列表 [英] Turn a value range in a cell into a comma separated list
本文介绍了将单元格中的值范围转换为逗号分隔列表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
以下A1是我可以输入的内容。 B1是一个公式。这可能吗?我将使A1始终遵循与XXX-XXX范围相同的格式。
+ ------- + ---------- + ---------------------- +
| TABLE | A(输入)| B(结果)|
+ ------- + ----------- + ---------------------- +
| 1 | 1-10 | 1,2,3,4,5,6,7,8,9,10 |
+ ------- + ----------- + ---------------------- +
解决方案
将代码放在常规VBA模块中,然后可以使用(例如):
= NumRange(A1)
在B1
函数NumRange(v)
Dim arr, x As Long,rv As String,sep As String
如果InStr(v, - )然后
arr = Split(v, - )
arr(0)= Trim arr(0))
arr(1)= Trim(arr(1))
如果IsNumeric(arr(0))和IsNumeric(arr(1))然后
对于x = CLng (arr(0))到CLng(arr(1))
rv = rv& SEP& x
sep =,
下一个x
如果
结束如果
NumRange = rv
结束函数
编辑 - 处理多个范围
函数NumRange(v)
/ pre>
Dim arrC,arr,x As Long,rv As String,sep As String,e
arrC = Split(v,, )
rv =
对于每个e在arrC
如果InStr(e, - )然后
arr = Split(e, - )
arr(0)= Trim(arr(0))
arr(1)= Trim(arr(1))
如果IsNumeric(arr(0))和IsNumeric(arr ))然后
对于x = CLng(arr(0))到CLng(arr(1))
rv = rv& SEP& x
sep =,
Next x
End If
ElseIf IsNumeric(e)Then
rv = rv& SEP& CLNG(e)
sep =,
结束If
下一步e
NumRange = rv
结束函数
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 | +-------+-----------+----------------------+
解决方案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
这篇关于将单元格中的值范围转换为逗号分隔列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文