5

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?

Improve this question
4
  • 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
    Commented Dec 29, 2009 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
    Commented Dec 29, 2009 at 8:19
  • Yes, it would be a pain entering those ' characters if there's a lot of data entry.
    – pavium
    Commented Dec 29, 2009 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
    Commented May 4, 2012 at 9:46

7 Answers 7

Reset to default
6

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.

Improve this answer
7
  • And not a freehand circle in sight!
    – pavium
    Commented Dec 29, 2009 at 8:13
  • The question super easy to answer, but I can imagine more people encountering this issue!
    – Ivo Flipse
    Commented Dec 29, 2009 at 8:20
  • Freehand circles added upon request
    – user1931
    Commented Dec 29, 2009 at 8:25
  • Hmmm, too bad the ugliest-freehand-joke made it to Super User too...
    – Arjan
    Commented Dec 29, 2009 at 8:32
  • Like @dkusleika pointed out in a comment at Joril's answer at superuser.com/questions/88870/… -- if things are not meant for calculations (like social security numbers) then one should probably not format as a number either?
    – Arjan
    Commented Dec 29, 2009 at 17:43
4

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)

Improve this answer
4
  • This makes calculations harder, I guess?
    – Arjan
    Commented Dec 29, 2009 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
    Commented Dec 29, 2009 at 15:31
  • I missed that part of the question. I think @dkusleika is right!
    – Arjan
    Commented Dec 29, 2009 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
    Commented Jan 3, 2010 at 11:37
4

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.

Improve this answer
1
  • +1 The only answer that actually addresses the specific question!
    – DaveParillo
    Commented Jan 2, 2010 at 19:03
2

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.

Improve this answer
0
2

Taken from https://stackoverflow.com/a/3992588/3664960, I think this is the fastest and easiest:

=TEXT(A1,"0000")

However the TEXT function is able to do other fancy stuff like date formating, aswell.

Improve this answer
1

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.

Improve this answer
1

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .