View Single Post
  #6   Report Post  
Posted to microsoft.public.excel.setup
Rosewood
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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?

"Mo" wrote:

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.
Mo

"George King" wrote in message
...
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.
--
George


"Rosewood" wrote:

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 are dropped and I
have
to re-format the cels to "text" and replace the zeros.

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