How can SS#s be displayed as text with all zeros and no dashes?

G

Guest

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?
 
P

Pete_UK

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top