Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

While trying to fill in social security numbers into Excel 2007, my dad found out that he couldn't make the numbers start with a zero (0).

Example: 0123456789 is turned into 123456789

What do I need to do in order for the leading zeros to stay as-is entered and not disappear?

share|improve this question
    
Can you enlighten me? Not being an American, I'm not sure what kinds of things you do with Social Security Numbers, but I wouldn't expect any calculation is involved. Couldn't you enter them as text and keep the leading 0, by prefixing a quote character? –  pavium Dec 29 '09 at 8:17
    
Excel's default number format takes away the 0, regardless of what you're doing with it. And yes a ' would probably solve it, but I wasn't aware of it before he encountered the issue and it's -1 for usability... –  Ivo Flipse Dec 29 '09 at 8:19
    
Yes, it would be a pain entering those ' characters if there's a lot of data entry. –  pavium Dec 29 '09 at 8:21
    
Changing the cell data type to text doesnt change anything, Excel still automatically drops the leading zero. I wonder why this is, obviously a fault in the program. What if the name of your business starts with a zero, this would be highly annoying to change custom with allt those '0's every time! –  user132206 May 4 '12 at 9:46

6 Answers 6

up vote 5 down vote accepted

If the numbers you're inserting all have a common length, you can set a custom format on them:

alt text

So if you wanted 123456789 to turn into 0123456789, you'd use the format 0000000000.

share|improve this answer
    
Thank you John! I'll accept yours instead ;-) –  Ivo Flipse Dec 29 '09 at 8:10
    
In a difference on seconds! haha, thanks! –  John T Dec 29 '09 at 8:11
    
And not a freehand circle in sight! –  pavium Dec 29 '09 at 8:13
    
The question super easy to answer, but I can imagine more people encountering this issue! –  Ivo Flipse Dec 29 '09 at 8:20
    
Freehand circles added upon request –  John T Dec 29 '09 at 8:25

We can't ignore zero as you know how it is important to us. You can add zero before any number by performing the following steps:

  1. Open the MS Excel worksheet.

  2. Write the number in the cell before you want to add zero.

  3. Right click the mouse in the cell, select Format Cells... option. Format Cells window will be displayed.

  4. Select the Numbers tab from the window then click Custom option from the left Category: section.

  5. Select 0 option from the Type section.

  6. Type the no. of zeroes as many as your number contains digit. For example, your number is 12345, so now you need to enter five zeros after the selected 0 in the type section i.e. 000000.

  7. Click OK. The number of zeros entered by you will be converted to digits and the result would be 012345

share|improve this answer

Excel has special formatting for Social Security Numbers.

  1. Select the cells where you want to type the SSN's
  2. Right click and choose "Format Cells"
  3. On the "Number" tab, choose "Special" (toward the bottom of the list)
  4. On the right you'll see "Social Security Number". Select it and click "OK"

When you type the SSN's in those cells, they will take leading zeros.

share|improve this answer
    
+1 The only answer that actually addresses the specific question! –  DaveParillo Jan 2 '10 at 19:03

You can always format the column as 'Text' before entering the values.
That way Excel does not try to guess if it is a number or not and will keep the leading zeros.

That saves the hassle of entering ' before entering the number.

share|improve this answer

An alternative is to type a quotation mark (') and then the number.. This way the number is treated as a string, so the leading zeros are kept. (The quote won't be displayed)

share|improve this answer
    
This makes calculations harder, I guess? –  Arjan Dec 29 '09 at 10:05
3  
You shouldn't be doing calculations on SSNs, I think is the point. Credit card #s, SSNs, and other ID #s should be entered as text because that's what they are. IMHO of course. –  dkusleika Dec 29 '09 at 15:31
    
I missed that part of the question. I think @dkusleika is right! –  Arjan Dec 29 '09 at 17:41
    
and if you need to do calculations on SSNs (or any other text field) you can always run it through a converter to get a number from the real data. –  quack quixote Jan 3 '10 at 11:37

I found the solution on a forum:

Your numbers are probably stores as numbers, if you still want them stored as numbers you could use custom number formatting.

Select Format -> Cell -> Number -> Custom

Enter this format: 00000000000

This makes your number display with 11 digits and leading zeros.

share|improve this answer

protected by Ivo Flipse May 25 '12 at 7:19

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.