Zero keeps disappearing in my Excel spreadsheet column

1000655 pts.
Excel 2007
Microsoft Excel
So I'm adding some of my personal information into an Excel 2007 workbook and I've noticed that when I put in a number count that starts with zero, it automatically disappears. Here's an example:
changes to
What do I have to do keep the zero as entered and not disappear.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Select the range of cells that will contain numbers with leading zeros.  Right click the group and select “Format Cells”.  Choose the Number tab.  Select “Text” from the list of categories.  Now, the information entered into the cells will be treated as plain text, and will appear exactly as entered.

Discuss This Question: 9  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • bhannah
    I suspect that range of cells has been set to not show leading zero's in the format. If that is the case, then when you enter data into that cell or cells it will delete the leading zero from the data. You will have to change the formatting of the range of cells by selecting the range of cells, right click and select format. This will be give you a number of options. You will have to find the option that fits you. I suspect that general will fit as this format does not delete leading zero's but leaves them in place.
    3,200 pointsBadges:
  • jinteik

    or another way is to put ' infront of 0

    so to use your example    '012345678

    18,955 pointsBadges:
  • cbrack
    Changing format to text will work, but it will prevent you from using it a number in functions. I suggest right clicking on the cell and going to format cells. Under formats click custom. Then under where it says type: enter enough zeros to cover the number of digits you want to display, for exampe if you want:
    your format should be

    And that's it, it will look the way you want it and you can use it like any other number in your worksheet
    10 pointsBadges:
  • pcarb07
    I done that but had to do it time and time again because it would revert back every time I done it
    10 pointsBadges:
  • ToddN2000
    Highlight all the cells, the select format and custom. Enter a zero for every position you want in order to keep the leading zeros. When done you need to save you sheet with the new formatting. If you add new data and it's in a cell you did not format you will have to format the new cells. It's probably best to format the column so all future data will follow the formatting.
    70,315 pointsBadges:
  • Terajo
    What do you mean by cells?
    10 pointsBadges:
  • CharlieBrowne
    A cell is one of the boxes on the spreadsheet that can contain data.
    62,280 pointsBadges:
  • jawadkhattak
    Worked.... superb.
    10 pointsBadges:
  • bantungxola
    Best solution:

    1...Copy and past to Note Pad or Note Pad ++...
    2...Copy and paste back to excel
    3...Paste option will appear next to the cell pasted on
    4...Under paste option select Use Text import wizard
    5...A Text import wizard appears
    6...Click next in the wizard
    7...Click next in the wizard text within Column Data format and click finish

    10 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: