Transposing list of values with 6 zeroes in front to retain the zero after transposing

This should do

Sub test()
    Dim x As String
    x = Range("a1", Range("a" & Rows.Count).End(xlUp)).Address
    [l4] = "'" & Join$(Evaluate("transpose(" & x & ")"), ",")
End Sub