Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SSN Conversion / Losing Leading Zeros (2000)

    In Excel, cells can have special formatting for Social Security Numbers.

    When one tries to convert this number to text, the leading zeros are immediately lost.

    This also happens when one tries to use the Text to Columns Data function, as well as when one tries to use Access to import.



    I would like to know any way/tip etc NOT TO LOSE LEADING ZEROS from SSN

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    Use the special formatting if it will be a number

    or to convert use something like:
    =Text(a1,"000-00-0000")
    to convert A1 into to a text string

    The best bet is always to use text for any "number" that is not a number.

    IMO, A number is something you will/can do math functions on. SSN, ZipCodes, phone numbers, should never be numbers: they should always be text.

    Steve

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    Thanks Steve,

    Im going to fiddle around and see what happens

  4. #4
    New Lounger
    Join Date
    Feb 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    OK.. it didn't quite work out the way I was hoping.

    For the record, I

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    I don't understand what you want to end up with. It looks like Steve's solution worked perfectly. You probably need one additional step to get the SSNs as text without the formula. Select the column with Steve's formula. Then select Copy from the Edit menu. Then select Paste Special from the Edit menu. In the dialog box, in the Paste section, Click Values. Then click OK. You should now have a column of SSNs as text values. Is that what you want? If not, what do you want to end up with?
    Legare Coleman

  6. #6
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    "As soon as you try and do ANYTHING to them,"

    Can you give us an example of what you are doing?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  7. #7
    New Lounger
    Join Date
    Feb 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    Other than the attachment I provided. I don't have another example. I had a call from someone who said that when he tries to convert the SS# to text, the zeros that are displayed in the beginning portion of the SS# disappear. Ultimately, he is just looking for a way to convert it to text without having the zeros disappear. I didn't want to misinterpret the question, so I had him email me, which is what I posted.

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    Isn't that what Steve's formula plus my extra step does?
    Legare Coleman

  9. #9
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    If you are doingf a Data | Text to Columns conversion to split the SSNs into three columns, you can retain the leading zeros if you use Delimited with '-' as the delimiter, then in the 3rd dialog select each of the three columns and set "Column data format" as Text. See graphic.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    New Lounger
    Join Date
    Feb 2004
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SSN Conversion / Losing Leading Zeros (2000)

    Thanks Fellas! It appears to be ok now...

Posting Permissions

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