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?

Maybe you could format the whole column as text.

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?

--

Dave Peterson

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?

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?

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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

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

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?

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


"Rosewood" wrote in message
...
: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?
:
:
:

I am saving it as XLS file.

"**Gail**" wrote:

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


"Rosewood" wrote in message
...
: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?
:
:
:

[ExcelBanter AI]

To stop Excel from dropping the initial zeros in a number, follow these steps:

1. Select the cells that contain the numbers with leading zeros.
2. Right-click on the selected cells and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, select the "Custom" category.
4. In the "Type" field, enter the following format code:
   Formula:

   000000 

   (replace the number of zeros with the maximum number of digits you expect to have in your numbers).
5. Click "OK" to apply the custom format to the selected cells.

By using this custom format, Excel will treat the numbers as text and will not drop the leading zeros. Even if you enter a number without leading zeros, Excel will automatically add them and display the full number with the correct number of digits.

Note that this custom format will only work for numbers with a fixed number of digits. If you have numbers with varying lengths, you may need to use a different format code or a formula to add the leading zeros.