Leading Zeros in Numeric Format

This is probably (hopefully!) a simple fix, but, after scouring the 'net for several minutes, I wasn't able to locate a viable solution.

I have a large column of SSNs (upwards of 40,000 records), many of which contain leading zeros. The dashes were removed, so the numbers appear as follows:

123456789

I used 'custom' formatting to add leading zeros; however, although Excel displays the zeros, it doesn't store them. The stored value for someone with a leading zero appears as follows:

12345678 (actual SSN would be 012345678).

I don't want to convert the format to text, as I'm submitting the file to a third party that requires the SSNs to be in numeric format. How can I force Excel to store leading zeros without converting to text?

Thanks in advance!

Hi,
As far as i know, you could try one of 2 things, but they all have the same problem.

(your data is in A1)
In B1 type =CONCATENATE(0,A1)
In B1 type ="0"&A1

This will show the info as number format, but its actually a formula, so to prevent any issues you would need to copy & paste special values, which then goes full circle back to the message - "The number in this cell is formatted as text or is proceeded by an apostrophe".

Beyond that im afraid i cant help.
galvinpaddy