Excel VBA To Concatenate

Question

from some googling I found this function that will concatenate the data in columns A, B & C based off the value in column D. This code does not work for me for some reason. My data looks like such

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

Columns, A and B concat fine, but column C concats to

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

How would the VBA be altered so that the code will concatenate properly?

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

You need to set the destination cells to a Text format:

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