Zero keeps disappearing in my Excel spreadsheet column

1021355 pts.
Tags:
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:
012345678
changes to
12345678
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: 16  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,485 pointsBadges:
    report
  • jinteik

    or another way is to put ' infront of 0

    so to use your example    '012345678

    18,965 pointsBadges:
    report
  • 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:
    00123
    your format should be
    00000

    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:
    report
  • 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:
    report
  • 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.
    79,325 pointsBadges:
    report
  • Terajo
    What do you mean by cells?
    10 pointsBadges:
    report
  • CharlieBrowne
    A cell is one of the boxes on the spreadsheet that can contain data.
    62,305 pointsBadges:
    report
  • jawadkhattak
    Worked.... superb.
    10 pointsBadges:
    report
  • 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
    8...select text within Column Data format and click finish

    10 pointsBadges:
    report
  • aidilazhars
    I have problem with my Microsoft Excel, I lost my coma when I copy and paste, for example:

    1.0234 become 10234

    also when I enter 1, the value change to 0.01.
    10 pointsBadges:
    report
  • ToddN2000
    When you copy and paste data, it take on the cell formatting of the destination where you are pasting it. If you still want the comma, decimal or any other formatting make sure to re-apply it in the new location cell.
    79,325 pointsBadges:
    report
  • Subhendu Sen
    Open the file in excel. Write the numbers that you wanted. Now right click the mouse in the cell & select format cells option. Select the numbers tab then click custom option from the left category. Again select 0 (zero) option from the type section. Now type zero and click OK.
    83,850 pointsBadges:
    report
  • carlosdl
    Asking a new question would probably be the best option for aidilazhars, to make sure his/her problem receives the appropriate attention.
    84,195 pointsBadges:
    report
  • eithaw
    How should I do for lot of cells not to disappear 0?
    10 pointsBadges:
    report
  • ToddN2000
    For multiple cells in a column, right click the column letter to highlight the entire column then select format cells and follow the instructions above. If it's just a group, left click a cell, hold shift a left click the last cell in the range to highlight them. Then right click the selected area and format the cells.
    79,325 pointsBadges:
    report
  • ArielAlzamoraMatos
    One thing you can do is add 0 (zeros) at the beginning of a customized cell format.

    Once, i had to input ids # in the form of ###-#######-#, among which there were those which the first 3 digits may include one or two leading zeroes, which had to be shown. I solved it customizing a cell format with two leading zeros, like this: 00#-#######-#, and it worked!
    10 pointsBadges:
    report

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.

Following

Share this item with your network: