Zero keeps disappearing in my Excel spreadsheet column

1161240 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.
1

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: 24  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.
    4,590 pointsBadges:
    report
  • jinteik

    or another way is to put ' infront of 0

    so to use your example    '012345678

    18,995 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.
    136,970 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,385 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.
    136,970 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.
    144,140 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.
    86,030 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.
    136,970 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
  • RichardinGT
    Try export to Notepad, save it. Then, on Excel go to DATA>import text from Text/CSV look for that saved doc. Try to copy back. Works for me.
    10 pointsBadges:
    report
  • tmoodley
    The only way to do this is to export the field like this "123", ="00001",

    Add an equals to sign with the value in quotes.
    10 pointsBadges:
    report
  • ToddN2000
    Another option of keeping the leading zeros is to format the cells as text or use a single quote before you enter the number(does the same thing). This won't work if you need numeric values for calculations though.
    136,970 pointsBadges:
    report
  • ARMAAN
    I have a file in the Microsoft Excel that has many columns in which there is no money. I want you to write 0 zero a formula in an Excel that is written in all the blank columns.
    10 pointsBadges:
    report
  • ToddN2000
    @Armaan: Where did the data come from? Was it imported into the spread sheet? 

    Select the range that you want to fill blank cells.

    Click Home > Find & Select > Go To Special…to open the Go To Special dialog box. Then choose Blanks option from Select section. 

    Then click OK. All of the blank cells will be selected. Don’t do anything yet, input the value you want, and then press Ctrl + Enter keys together, and all of the blank cells have been filled with your new value
    136,970 pointsBadges:
    report
  • Subhendu Sen
    @Armaan, if there Zero is written, then you can copy that and paste on another blank columns. Or you can use Paste Special option which is available in Excel.
    144,140 pointsBadges:
    report
  • qtapioca
    I have a column that has values like
    01.01
    01.02
    01.03
    01.04
    01.05
    01.06
    0101.2
    0101.21.00
    0101.21.00.00 etc

    Right now the format is "General".  I converted it to text.  However, when I tried to replace the "." in the 01.01 it became 101. How can I do a replace without getting the 0 removed?  
    10 pointsBadges:
    report
  • ToddN2000
    @qtapioca: if there is no consistency in the format and you do not need to do calculations on it, your best bet may be to define is as text in Excel. The problem you may have is during the import process. You may have to convert it ti text beforehand.
    136,970 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: