vba - Excel VBA 连接

Question

通过一些谷歌搜索，我发现这个函数将根据 D 列中的值连接 A、B 和 C 列中的数据。由于某种原因，这段代码对我不起作用。我的数据看起来像这样

Bob   Jason   0123456789  Tim
Jim   Jason   0123456789  Tim
Fred  Jason   0123456789  Tim

A 列和 B 列连接良好，但 C 列连接到

12,345,678,901,234,500,000,000,000,000

如何更改 VBA 以使代码正确连接？

Sub Concat()
    Dim x, i As Long, ii As Long
    With Cells(1).CurrentRegion
        x = .Columns("d").Offset(1).Address
        x = Filter(Evaluate("transpose(if(countif(offset(" & x & ",,,row(1:" & .Rows.Count & "))," & x & ")=1," & x & "))"), False, 0)
        For i = 0 To UBound(x)

            For ii = 1 To 3
                Cells(i + 2, ii + 5).Value = Join(Filter(Evaluate("transpose(if(" & .Columns(4).Address & "=""" & _
                    x(i) & """," & .Columns(ii).Address & "))"), False, 0), ",")
            Next
            Cells(i + 2, ii + 5).Value = x(i)
        Next
    End With
End Sub

Answer 1

您需要将目标单元格设置为文本格式:

Sub Concat()
    Dim x, i As Long, ii As Long
    With Cells(1).CurrentRegion
        x = .Columns("d").Offset(1).Address
        x = Filter(Evaluate("transpose(if(countif(offset(" & x & ",,,row(1:" & .Rows.Count & "))," & x & ")=1," & x & "))"), False, 0)
        For i = 0 To UBound(x)

            For ii = 1 To 3
                Cells(i + 2, ii + 5).NumberFormat = "@"
                Cells(i + 2, ii + 5).Value = Join(Filter(Evaluate("transpose(if(" & .Columns(4).Address & "=""" & _
                    x(i) & """," & .Columns(ii).Address & "))"), False, 0), ",")
            Next
            Cells(i + 2, ii + 5).NumberFormat = "@"
            Cells(i + 2, ii + 5).Value = x(i)
        Next
    End With
End Sub