How do I force trailing zeros in an Excel cell? Tony C 9/13/2006 7:55:02 PM 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!

Re: How do I force trailing zeros in an Excel cell? "Bernard Liengme" <bliengme[ at ]stfx.TRUENORTH.ca> 9/13/2006 8:04:25 PM 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 C[ at ]discussions.microsoft.com> wrote in message news:3B596CD7-6C86-4F7E-BC49-922C35A49E93[ at ]microsoft.com... [Quoted Text] > 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!

Re: How do I force trailing zeros in an Excel cell? Tony C 9/13/2006 8:16:02 PM 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: [Quoted Text] > 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 C[ at ]discussions.microsoft.com> wrote in message > news:3B596CD7-6C86-4F7E-BC49-922C35A49E93[ at ]microsoft.com... > > 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! > > >

Re: How do I force trailing zeros in an Excel cell? gls858 <gls858[ at ]yahoo.com> 9/13/2006 8:22:53 PM Tony C wrote: [Quoted Text] > 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

Re: How do I force trailing zeros in an Excel cell? Tony C 9/13/2006 8:35:01 PM 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: [Quoted Text] > 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 >

Re: How do I force trailing zeros in an Excel cell? Gord Dibben <gorddibbATshawDOTca> 9/13/2006 9:21:59 PM 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 <TonyC[ at ]discussions.microsoft.com> wrote: [Quoted Text] >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 C[ at ]discussions.microsoft.com> wrote in message >> news:3B596CD7-6C86-4F7E-BC49-922C35A49E93[ at ]microsoft.com... >> > 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! >> >> >>

Re: How do I force trailing zeros in an Excel cell? gls858 <gls858[ at ]yahoo.com> 9/13/2006 9:30:46 PM Tony C wrote: [Quoted Text] > 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

Re: How do I force trailing zeros in an Excel cell? Dave Peterson <petersod[ at ]verizonXSPAM.net> 9/13/2006 9:40:59 PM 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: [Quoted Text] > > 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

Re: How do I force trailing zeros in an Excel cell? SteveW <sj_walton[ at ]nothotmail.com> 9/14/2006 7:43:32 AM 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 <gls858[ at ]yahoo.com> wrote: [Quoted Text] > Tony C wrote: >> that is exactly how my accounting people created the numeric column b= y = >> setting the decimal place to 5, and not all my part number will alway= s = >> 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 tab= le >> or import it into something.. >> I need it to really be 2201.11520 , so its always retains that traili= ng = >> 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 goe= s = >>>> away on the edit line. >>>> When I copy and past this numeric column into other Excels or impor= t = >>>> 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 coul= d >>> 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 tex= t > 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 num= ber > and then reformat it to text, no trailing zero. >