Excel连接范围内的每个单元格 [英] Excel Concatenate Each Cell in Range
本文介绍了Excel连接范围内的每个单元格的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
一个公式返回范围开始的单元格地址,例如:
'[AM Track.xlsm]原始数据'$ A $ 10`
另一个公式返回范围结束的单元格地址例如
'[AM Track.xlsm]原始数据'!$ A $ 21`
如何连接这个范围内的每个单元格?
解决方案
感谢对Excel 2016中的TextJoin的评论,我发现这一点:
Option Explicit
功能TEXTJOIN(分隔符As String,ignore_empty As String,ParamArray textn()As Variant)As String
Dim i As Long
For i = LBound(textn)To UBound(textn) - 1
如果Len(textn (i))= 0然后
如果不是ignore_empty = True然后
TEXTJOIN = TEXTJOIN& textn(i)&分隔符
End If
Else
TEXTJOIN = TEXTJOIN& textn(i)&分隔符
End If
Next
TEXTJOIN = TEXTJOIN& textn(UBound(textn))
结束函数
I have two formulas that find the range that I would like to concatenate. One formula returns the cell address for the start of the Range for example:
'[AM Track.xlsm]Raw Data'!$A$10`
The other formula returns the cell address for the end of the Range e.g.
'[AM Track.xlsm]Raw Data'!$A$21`
How can I concatenate each cell in this range?
解决方案
Thanks to the comment about TextJoin in Excel 2016 I found this:
Option Explicit
Function TEXTJOIN(delimiter As String, ignore_empty As String, ParamArray textn() As Variant) As String
Dim i As Long
For i = LBound(textn) To UBound(textn) - 1
If Len(textn(i)) = 0 Then
If Not ignore_empty = True Then
TEXTJOIN = TEXTJOIN & textn(i) & delimiter
End If
Else
TEXTJOIN = TEXTJOIN & textn(i) & delimiter
End If
Next
TEXTJOIN = TEXTJOIN & textn(UBound(textn))
End Function
这篇关于Excel连接范围内的每个单元格的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文