Hi,

I have a list of about 2,000 social security numbers in excel. Some are
formatted with an apostrophe to preserve the first zero. I need them all to
be a string of nine numbers with no dashes and to preserve the first zero in
the formula bar. The Custom formatting dispays the zero on the worksheet but
not in the formula bar. How can I format all the cells to preserve the first
zero and look the way I need them to without adding the apostrophe to all?

You could make use of a helper column and use this formula:

=TEXT(A1,"000000000")

if your numbers are in column A. Copy this formula down to row 2000,
then fix the values - these can then be copied over the originals in
column A and the helper column deleted.

Hope this helps.

Pete

latwood wrote:
Hi,

I have a list of about 2,000 social security numbers in excel. Some are
formatted with an apostrophe to preserve the first zero. I need them all to
be a string of nine numbers with no dashes and to preserve the first zero in
the formula bar. The Custom formatting dispays the zero on the worksheet but
not in the formula bar. How can I format all the cells to preserve the first
zero and look the way I need them to without adding the apostrophe to all?