excel - 在系列中的每个单元格值之间附加字符串

Question

假设我有一系列像这样的单元格:

    A
1  Foo
2  Bar
3  Hello
4  World
5  Random Text

我想做的是将公式的结果填充到另一个单元格中:

Foo, Bar, Hello, World, Random Text

现在，我知道如何连接两个单元格:

=A1&", "&A2

但是我怎样才能对整个系列做同样的事情呢？

Answer 1

这是一个您可能会使用的函数。只需将其放入您的工作簿代码模块中，然后您就可以在单元格中输入它，例如:

=JoinRange(A1:A6) 或 =JoinRange(A2:D15) 等

Public Function JoinRange(ByVal rng As Range) As String
    Dim dlmt As String: dlmt = ","
    Dim multiRow As Boolean: multiRow = rng.Rows.Count > 1
    Dim r As Long, c As Long

    Select Case rng.Columns.Count
        Case 1
            If multiRow Then
                JoinRange = Join(Application.WorksheetFunction.Transpose(rng), dlmt)
            Else:
                'a single cell
                JoinRange = rng
            End If
        Case Is > 1
            If multiRow Then
                'a 2d range of cells:
                For r = 1 To rng.Rows.Count
                    For c = 1 To rng.Columns.Count
                        JoinRange = JoinRange & rng(r, c) & dlmt
                    Next
                Next
                JoinRange = Left(JoinRange, Len(JoinRange) - 1)

            Else:
                JoinRange = Join(Application.WorksheetFunction.Transpose( _
                                Application.WorksheetFunction.Transpose(rng)), dlmt)
            End If
        Case Else

     End Select

End Function