Hello,
I would like to add leading zero’s to VBA and I’m not having much luck. What I am trying to do is break up a line of text and then make sure to add leading zero’s to an official ID. When I write a normal formula it would be something like =text(a1,”000000000000”) but when I add this to the VBA it just puts that part of the VBA in the cell. I need the numeric part of the ID to be 12 digits.
For example, HOCAN123456789012.

I have attached a spreadsheet for reference and the code below as well.

Thank you in advance!


Dim a(15) As String, c As Range
With Sheets("CDN")
    For Each c In .Range("a1", .Cells(Rows.Count, "A").End(xlUp))leading zero.xlsm
        With c
            a(0) = Mid(.Value, 106, 10)
            a(1) = Mid(.Value, 43, 12)
            a(2) = Mid(.Value, 85, 8)
            a(3) = Mid(.Value, 55, 30)
            a(5) = Mid(.Value, 159, 5) & "=text(Mid(.Value, 165, 12), ""000000000000"")"
            a(8) = Mid(.Value, 207, 18)
            a(9) = Mid(.Value, 225, 30)
            a(11) = Mid(.Value, 264, 5) & "=text(Mid(.Value, 270, 12), ""000000000000"")"
            a(14) = Mid(.Value, 486, 18)
            a(15) = Mid(.Value, 504, 30)
            .Offset(, 1).Resize(, 16) = a
            End With
        Next
End With