Preserve leading zeros in a varying length number when converting it to text

Hi, I've been struggling with this for a while now, hopefully someone will be able to help.

I have a column of numbers of varying lengths that may or may not have leading zeros. Using VBA I need to concatenate the column to another text column.

the number column might contain
00123
0120
0000043200
2453230
001234567890

Sometimes the cell contents are formatted as general, sometimes numbers, sometimes they have the special formatting set to a certain length of number e.g. 0000000.
Whichever way the cell is formatted I need to convert it to text while keeping the leading zeros.
I'm converting a customers worksheet and can't control how they're entering their data.

I need to preserve the length of the number, can't have them all being 12 characters long.

Thanks though

=TEXT(A1,REPT("0",LEN(A1)))

VBA?
Example

Please Login or Register  to view this content.

Thanks jindon, that works great!