Macro Help replacing dropped zeros

[fixer33]

I have a complicated problem and I hope I can get help to write a macro
to fix it, I am dealing with the dreaded zero dropping problem, I have
tried everything, formatting as text etc, I think my problem is that in
the file exported from access, it is dropping the zeros, the numbers
appear properly in the cell but up in the formula bar the zeros are
missing, I need to cut and paste these numbers into another spreadsheet
that gets pulled into another access program. It errors out if the
zeros are missing.
If I manually add the zeros in the formula bar and put an apostrophe in
front everything works fine.
I have thousands of these numbers and they all must be 9 charachters.
These are national stock numbers, I am thinking the macro that I need
will add zeros to equal nine charachters and put an apostrophe in
front. I this possible? Some numbers may be missing 1 zero and others
may be missing 4 zeros and I need to put them back to equal 9
charachters.
I have pretty basic excel skills, anyone willing to help me with this?
I do not want to do this manually if I can help it.
Thanks,
Darrell

 

[Ken Johnson]

Hi,

Will a worksheet formula do?...

=REPT(0,9-LEN(A1)) & A1

I don't think you have to bother with the apostrophe.

Ken Johnson

 

[Ken Johnson]

If I'm wrong about the apostrophe then ="'" & REPT(0,9-LEN(A1)) &A1

Ken Johnson

 

[Guest]

Darrell,
Would this suffice:

Put this formula in a helper column, copy down and then copy/paste special
values

=REPT("0",9-LEN(A1))&A1

OR

Cells formatted as text. Select you range and run macro.

Sub AddZeros()
For Each cell In Selection
cell.Value = Left("000000000", 9 - Len(cell)) & cell.Value
Next
End Sub

HTH

 

[Ken Johnson]

Hi,
If it has to be a macro this works if you run it after selecting the
range of cells...

Public Sub add_zeros()
Dim vaData As Variant
vaData = Selection
Dim I As Long
Dim J As Long
For I = 1 To UBound(vaData, 1)
For J = 1 To UBound(vaData, 2)
vaData(I, J) = "'" & _
WorksheetFunction.Rept("0", 9 - Len(vaData(I, J))) _
& vaData(I, J)
Next J
Next I
Selection = vaData
End Sub

Ken Johnson

 

[fixer33]

Woohoo, thanks for all the help, had to sleep for a while, Toppers
macro works fine, I cant express my appreciation, this really saved me.
Darrell