A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Setting up and Configuration of Excel
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Stop excel from dropping the 0 in the beginning of a number?



 
 
Thread Tools Display Modes
  #1  
Old January 25th 06, 09:51 PM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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?
Ads
  #2  
Old January 25th 06, 10:23 PM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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
  #3  
Old January 28th 06, 10:43 PM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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?

  #4  
Old February 4th 06, 02:07 AM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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?



  #5  
Old February 5th 06, 03:07 AM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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.





  #6  
Old February 6th 06, 03:15 PM posted to microsoft.public.excel.setup
external usenet poster
 
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?

>
>
>

  #7  
Old February 6th 06, 06:18 PM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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?
: >
: >
: >


  #8  
Old February 7th 06, 09:43 PM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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?
> : >
> : >
> : >
>
>
>

  #9  
Old February 8th 06, 12:23 PM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

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

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



  #10  
Old February 9th 06, 05:16 PM posted to microsoft.public.excel.setup
external usenet poster
 
Posts: n/a
Default Stop excel from dropping the 0 in the beginning of a number?

I format the Excel ISBN column to the 10 zeros, everything is cool; I set
the column to "text", and save it, first to Excel, and then to the tab
delimited .txtfile.
When I open the file I saved in the .xls format, the ten digits appear with
the leading zero.
Then I open the .txt file in Excel using the text import wizard, and in Step
3, set the column to be imported as text, the numbers are fine, all ten
digits appear.
Okay, then I open the same .txt file in Excel, but make no changes in the
text import wizard. The zeros have disappeared, the column has shrunk, so
that there is garbage in it, but when I expand it, I get the ISBN numbers,
WITHOUT the leading zeros.
My problem is that it is the .txt file I upload to Amazon, and they are
getting it without the leading zero. I have communicated ad nauseum with
them, and they keep giving me the same advice, which doesn't work.
Thanks to you all for the advice. I appreciate it.
Mo

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

>
>



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 06:16 PM
How can i paste a 16 digit number in a excel worksheet? Rajeshkumar Excel Discussion (Misc queries) 2 January 21st 06 11:19 AM
How do I stop last number from changing to a 0 in excel dan12 Excel Discussion (Misc queries) 3 January 10th 06 08:41 PM
Excel Range Value issue (Excel 97 Vs Excel 2003) Keeno Excel Discussion (Misc queries) 2 June 13th 05 02:01 PM
Difference in number of Excel NewsGroups Hari Prasadh Excel Discussion (Misc queries) 1 January 25th 05 11:32 AM


All times are GMT +1. The time now is 10:48 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.