Results 1 to 8 of 8

Thread: How can I stop the program from dropping the initial zeros in Excel

  1. #1
    Join Date
    Jan 2012
    Posts
    30

    How can I stop the program from dropping the initial zeros in Excel

    I do a lot of spreadsheet with number that start with one or more zeros. Not matter how many times I change the cel format to "text" so that the zeros are not dropped, I find that as I go down the sheet, they re dropped and I haveto re-format the cels to "text" and replace the zeros.

    How can I stop the program from dropping the initial zeros?

  2. #2
    Join Date
    Jul 2011
    Posts
    434

    Re: How can I stop the program from dropping the initial zeros in Excel

    Three ways:
    1 - You can create a custom format (Format, Cells, Custom) and enter the number of zeros that you want to see digits for.Example, if you want to see a total of 4 digits, even if you only enter 2 or 3, enter a custom format like "0000". Excel will display "0012" when you enter 12. Note that Excel only stores the 12, but displays it as 0012.

    2 - Format the cell as Text

    3 - Enter an apostorphe first, then the number - like '0012 Excel will not display the apostrophe, just the 0012. Note that Excel will treat this as text, not a number - won't calculate anything when it's formated as text.

  3. #3
    Join Date
    Jan 2012
    Posts
    17

    Re: How can I stop the program from dropping the initial zeros in Excel

    This is exactly the problem I have in trying to save ISBN numbers which often begin with a zero. I have tried all three methods you have suggested. Once the text in the column appears correct, I save it in the xls format, and can open it in excel correctly. But then I save it into the tab delimited.txt file. When I open the text file in wordpad, I can see the numbers are fine, with the leading zero correctly in place. Then I open
    the file again in excel, and the zeros have been dropped. This is a problem, because I must upload my books in a tab delimited file to Amazon, but the zeros disappear, so they don't load my books.

  4. #4
    Join Date
    Aug 2011
    Posts
    540

    Re: How can I stop the program from dropping the initial zeros in Excel

    That is hard to believe that you have to enter books with ISBN numbers beginning with a zero to Amazon, the data is there and they won't accept it. I would certainly ask Amazon about that. I think you should be including hyphens in those numbers not as part of formatting but change them to text with the hyphens. (just a guess)

    a1: 0123456789
    b1: =TEXT(A1,"0-0000-0000-0")

    BTW, I sure others might want to know if that is the solution or something else.

  5. #5
    Join Date
    Jan 2012
    Posts
    30

    Re: How can I stop the program from dropping the initial zeros in Excel

    I am glad that someone else has also tried the 3 solutions that were suggested without success. In my case, the some of the numbers start with zeros and some do not, depending on the OEM- which eliminates the "special formatting" option. Unfortunately, the various OEM's are not isolated, but combined in the same columns. Also, if the number is alpha-numeric, Excel sometimes changes the format to scientific- even after the sheet has been proofed and saved.

    Is there no way to format all the cells in a sheet in the "text" format and
    make it stick?

  6. #6
    Join Date
    Aug 2011
    Posts
    564

    Re: How can I stop the program from dropping the initial zeros in Excel

    How are you saving the file? Are you saving as XLS or resaving as CSV or TXT

  7. #7
    Join Date
    Jan 2012
    Posts
    30

    Re: How can I stop the program from dropping the initial zeros in Excel

    I am saving it as XLS file.

  8. #8
    Join Date
    Aug 2011
    Posts
    540

    Re: How can I stop the program from dropping the initial zeros in Excel

    You could convert to text directly, but it would not help your situation. Select the cells you want formatted as Text -- generally an entire column. Format, Cells, Number, Text Even though you have formatted as text they do not instantly become text. They become text when you reenter the information, i.e. F2, Enter

    However --
    Since your problem is one of missing zeros the above is not going to help you, because the number not the formatted number will be converted to text.

    I previously suggested use of a helper (intermediate) column
    B1: =TEXT(A1,"0-0000-0000-0")
    You would then have to convert that helper column to text as formatted, and therein lies the problem. You will need a macro to convert the helper column to values based on it's text value. Or you could start
    by formatting the original column with Format, Cells, Number, custom, 0-0000-0000-0. Then format as text which will be ignored until reentered.

    Then select the column Copy (Ctrl+C), Edit, Paste Special, Values

    You can do the entire thing at once with a macro, a macro that could be easily modified is
    http://www.mvps.org/dmcritchie/excel/join.htm#fixUSzip5 simply change the format, and change or eliminate length testing.

Similar Threads

  1. How to Format a cell to keep leading zeros in Microsoft Excel
    By Raju Chacha in forum MS Office Support
    Replies: 3
    Last Post: 14-01-2012, 07:05 PM
  2. How to set up Excel Alphabetize in an Excel program
    By Megatruck in forum Windows Software
    Replies: 4
    Last Post: 25-11-2010, 11:56 PM
  3. java program to add leading zeros to a number.
    By kamina23 in forum Software Development
    Replies: 4
    Last Post: 05-02-2010, 07:15 PM
  4. EXCEL not display leading zeros
    By Aasha in forum Windows Software
    Replies: 3
    Last Post: 22-10-2009, 06:21 PM
  5. Excel problem: Excel has to stop working...
    By void in forum Windows Software
    Replies: 5
    Last Post: 17-12-2008, 06:00 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,672,961.06716 seconds with 17 queries