+ Reply to Thread
Results 1 to 3 of 3

Leading Zeros in Numeric Format

  1. #1
    Registered User
    Join Date
    04-12-2012
    Location
    New York
    MS-Off Ver
    Excel 2007
    Posts
    1

    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!

  2. #2
    Forum Contributor
    Join Date
    11-02-2011
    Location
    Rugby, England
    MS-Off Ver
    Office 365
    Posts
    846

    Re: Leading Zeros in Numeric Format

    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

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    Re: Leading Zeros in Numeric Format

    Numbers don't include leading zeros. The receiving software should either understand that SSNs shorter than 9 digits should have leading zeros added, or accept them as text. I can't imagine a third alternative.
    Entia non sunt multiplicanda sine necessitate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1