How do you stop excel removing the leading zeros in a cell?

[Guest]

I need the leading zero to be actually in the cell so formatting the cell
using custom or special will not work as these seem to be for display only.
The cell cannot be formatted for text or you cannot put an apostrophe before
the number as the spreadsheet is read into a database table. The table field
is set up to take characters but does not accept anything with the green mark
in the top left of the cell.

 

[Don Guillett]

try a custom format of 000000000000 for the number you want to the limit

 

[Guest]

Add a column with formula of =text(b2,"000000000") zeros for the length you
want. This is not formatting, it changes it to text. It will show a green
mark in the top left but will not have an apostrophe. Copy and paste as
values over your original column and delete the new one.