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

" wrote:

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