How can SS#s be displayed as text with all zeros and no dashes?
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? |
How can SS#s be displayed as text with all zeros and no dashes?
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? |
All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com