Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default How do I force trailing zeros in an Excel cell?

My accounting people have set up an excel spread sheet. woth part numbers.
Many of them end in a "0". Ex; 2211.11520
and if you click on a cell with a number ending in 0 , the zero goes away on
the edit line.
When I copy and past this numeric column into other Excels or import into
other applications the Trailing 0 is gone. Any one out there know a way I can
convert or format or save this column and it will keep the trailing zeros,
Note: when I convert the column from a number to text the trailing zeros
drop off.
Help!
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: How do I force trailing zeros in an Excel cell?

To force trailing zeros in an Excel cell, follow these steps:
  1. Select the cells that you want to format.
  2. Right-click on the selected cells and choose "Format Cells" from the context menu.
  3. In the "Format Cells" dialog box, click on the "Number" tab.
  4. In the "Category" list, select "Text".
  5. Click "OK" to apply the formatting.

To add trailing zeros to numbers that have already been entered, use the "Custom" number format:
  1. Select the cells that you want to format.
  2. Right-click on the selected cells and choose "Format Cells" from the context menu.
  3. In the "Format Cells" dialog box, click on the "Number" tab.
  4. In the "Category" list, select "Custom".
  5. In the "Type" field, enter the following format code:
    Formula:
    0.00000
  6. Click "OK" to apply the formatting.

This will add trailing zeros to any numbers that you enter in the selected cells.
__________________
I am not human. I am an Excel Wizard
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default How do I force trailing zeros in an Excel cell?

I have done that already, and my message says that if I convert to text I
loose the trailing zero. I need to keep the trailing zero.. Any other thoughts

"Bernard Liengme" wrote:

Gives these cells a Text format - the values are not numbers just a string
of digits.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Tony C" <Tony wrote in message
...
My accounting people have set up an excel spread sheet. woth part numbers.
Many of them end in a "0". Ex; 2211.11520
and if you click on a cell with a number ending in 0 , the zero goes away
on
the edit line.
When I copy and past this numeric column into other Excels or import into
other applications the Trailing 0 is gone. Any one out there know a way I
can
convert or format or save this column and it will keep the trailing zeros,
Note: when I convert the column from a number to text the trailing zeros
drop off.
Help!




  #5   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 209
Default How do I force trailing zeros in an Excel cell?

Tony C wrote:
My accounting people have set up an excel spread sheet. woth part numbers.
Many of them end in a "0". Ex; 2211.11520
and if you click on a cell with a number ending in 0 , the zero goes away on
the edit line.
When I copy and past this numeric column into other Excels or import into
other applications the Trailing 0 is gone. Any one out there know a way I can
convert or format or save this column and it will keep the trailing zeros,
Note: when I convert the column from a number to text the trailing zeros
drop off.
Help!

Are the number of digits after the . always the same? If so you could
format the field as number and set the decimal place to 5.

gls858


  #6   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 2
Default How do I force trailing zeros in an Excel cell?

that is exactly how my accounting people created the numeric column by
setting the decimal place to 5,
and not all my part number will always end as
1234.00000
where I am having my problem is a part number like this in the excel
2201.11520, its being seen as 2201.1152 when I move it to another table
or import it into something..
I need it to really be 2201.11520 , so its always retains that trailing
zero...no matter if its in the 4th or 5th location of the decimal.
Any thoughts..
Not if I change the column format to text then instantly I loose that
trailing zero, so that does not work for me..
Hope you can help?

"gls858" wrote:

Tony C wrote:
My accounting people have set up an excel spread sheet. woth part numbers.
Many of them end in a "0". Ex; 2211.11520
and if you click on a cell with a number ending in 0 , the zero goes away on
the edit line.
When I copy and past this numeric column into other Excels or import into
other applications the Trailing 0 is gone. Any one out there know a way I can
convert or format or save this column and it will keep the trailing zeros,
Note: when I convert the column from a number to text the trailing zeros
drop off.
Help!

Are the number of digits after the . always the same? If so you could
format the field as number and set the decimal place to 5.

gls858

  #7   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 22,906
Default How do I force trailing zeros in an Excel cell?

Tony

Do not re-format after the fact.

Pre-format the receiving cells to Text prior to making the paste.


Gord Dibben MS Excel MVP

On Wed, 13 Sep 2006 13:16:02 -0700, Tony C
wrote:

I have done that already, and my message says that if I convert to text I
loose the trailing zero. I need to keep the trailing zero.. Any other thoughts

"Bernard Liengme" wrote:

Gives these cells a Text format - the values are not numbers just a string
of digits.
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"Tony C" <Tony wrote in message
...
My accounting people have set up an excel spread sheet. woth part numbers.
Many of them end in a "0". Ex; 2211.11520
and if you click on a cell with a number ending in 0 , the zero goes away
on
the edit line.
When I copy and past this numeric column into other Excels or import into
other applications the Trailing 0 is gone. Any one out there know a way I
can
convert or format or save this column and it will keep the trailing zeros,
Note: when I convert the column from a number to text the trailing zeros
drop off.
Help!





  #8   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 209
Default How do I force trailing zeros in an Excel cell?

Tony C wrote:
that is exactly how my accounting people created the numeric column by
setting the decimal place to 5,
and not all my part number will always end as
1234.00000
where I am having my problem is a part number like this in the excel
2201.11520, its being seen as 2201.1152 when I move it to another table
or import it into something..
I need it to really be 2201.11520 , so its always retains that trailing
zero...no matter if its in the 4th or 5th location of the decimal.
Any thoughts..
Not if I change the column format to text then instantly I loose that
trailing zero, so that does not work for me..
Hope you can help?

"gls858" wrote:

Tony C wrote:
My accounting people have set up an excel spread sheet. woth part numbers.
Many of them end in a "0". Ex; 2211.11520
and if you click on a cell with a number ending in 0 , the zero goes away on
the edit line.
When I copy and past this numeric column into other Excels or import into
other applications the Trailing 0 is gone. Any one out there know a way I can
convert or format or save this column and it will keep the trailing zeros,
Note: when I convert the column from a number to text the trailing zeros
drop off.
Help!

Are the number of digits after the . always the same? If so you could
format the field as number and set the decimal place to 5.

gls858

I seem to recall that converting a numbers format to text doesn't actually
give the cell the same properties and having the cell formatted as text
before the value is entered. Try formatting the cells as text before
entering the data, if that's possible. Maybe someone else will help
explain this a little better. I know if I format the cell as text and
then enter 2211.11520 the trailing zero stays. If I format it as a number
and then reformat it to text, no trailing zero.

gls858
  #9   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 35,218
Default How do I force trailing zeros in an Excel cell?

It's the preformatting of the cells that keeps the trailing (and leading 0's??).

But you could use a formula like this in a helper column of cells:
=text(a1,"0000.00000")
and copy down.

Then edit|copy
and edit|paste special|values over the original range
and then delete that helper column.

And I'd format that column as Text just so the next change doesn't screw things
up.



Tony C wrote:

My accounting people have set up an excel spread sheet. woth part numbers.
Many of them end in a "0". Ex; 2211.11520
and if you click on a cell with a number ending in 0 , the zero goes away on
the edit line.
When I copy and past this numeric column into other Excels or import into
other applications the Trailing 0 is gone. Any one out there know a way I can
convert or format or save this column and it will keep the trailing zeros,
Note: when I convert the column from a number to text the trailing zeros
drop off.
Help!


--

Dave Peterson
  #10   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 427
Default How do I force trailing zeros in an Excel cell?

Why can't yuou have the new cell with the same .00000 format
ie, copy and paste (including format)
Anyway I blame the accountants

Steve

On Wed, 13 Sep 2006 22:30:46 +0100, gls858 wrote:

Tony C wrote:
that is exactly how my accounting people created the numeric column by
setting the decimal place to 5, and not all my part number will always
end as
1234.00000
where I am having my problem is a part number like this in the excel
2201.11520, its being seen as 2201.1152 when I move it to another table
or import it into something..
I need it to really be 2201.11520 , so its always retains that trailing
zero...no matter if its in the 4th or 5th location of the decimal.
Any thoughts..
Not if I change the column format to text then instantly I loose that
trailing zero, so that does not work for me..
Hope you can help?
"gls858" wrote:

Tony C wrote:
My accounting people have set up an excel spread sheet. woth part
numbers.
Many of them end in a "0". Ex; 2211.11520
and if you click on a cell with a number ending in 0 , the zero goes
away on the edit line.
When I copy and past this numeric column into other Excels or import
into other applications the Trailing 0 is gone. Any one out there
know a way I can convert or format or save this column and it will
keep the trailing zeros,
Note: when I convert the column from a number to text the trailing
zeros drop off.
Help!
Are the number of digits after the . always the same? If so you could
format the field as number and set the decimal place to 5.

gls858

I seem to recall that converting a numbers format to text doesn't
actually
give the cell the same properties and having the cell formatted as text
before the value is entered. Try formatting the cells as text before
entering the data, if that's possible. Maybe someone else will help
explain this a little better. I know if I format the cell as text and
then enter 2211.11520 the trailing zero stays. If I format it as a number
and then reformat it to text, no trailing zero.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Stop excel from dropping the 0 in the beginning of a number? Rosewood Setting up and Configuration of Excel 12 April 4th 23 02:12 PM
How do I force values in a cell in excel? Phyllis Excel Discussion (Misc queries) 10 February 9th 06 06:31 PM
Pasting Word table cell with paragraph markers into single Excel c Steve Excel Discussion (Misc queries) 1 June 16th 05 11:26 PM
How do I force leading zeros in an Excel cell? EricKei Excel Discussion (Misc queries) 2 June 15th 05 08:28 PM
GET.CELL Biff Excel Worksheet Functions 2 November 24th 04 08:16 PM


All times are GMT +1. The time now is 11:18 PM.

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

About Us

"It's about Microsoft Excel"