VBA Code to keep leading zeros when concatenating 2 columns.

[Coder119]

Looking for VBA Code that can get leading zeros to stay when concatenating 2 columns using VBA? I already used the number format "000000000" in VBA code to get the one column to add the preceding zero when needed or keep the leading zero when removing dashes, but when I do the VBA code to concatenate them it drops the zero. So below I have some different sceniors in Number 1 of what I might see for data and the number 1 column would be concatenated with the items colum in a new column called Concatenate at least that is what my code to concatenate does.

 

[Joe4]

Do you really need VBA to do this?
It is not a VBA issue that is causing the leading zeroes to drop. Concatenating with native Excel formulas will have the same issue.
It is because concatenate only affects the actual values, and does not consider the formatting applied to the cell (formatting just changes the view shown, not the underlying value).

Just like you use Formatting to show the leading zeroes on the sheet, you can use the TEXT function to show those when concatenating, i.e.

=TEXT(A1,"000000000") & ...

In VBA, the FORMAT function works the same way as the TEXT function does in Excel, i.e.

TEXT(Range("A1").Value,"000000000") & ...