Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill with zero
Hi,
I table of 3 digit numeric fields, data in field can be 1-3 significant digits. I use the numbers as input to another look up that requires all 3 digits and filled with zeros. So I need a formula to determine number of significant digits in original field and if less than 3 fill to 3 with zeros. I tried "format" as well as "precision as displayed", but niether worked. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill with zero
To get 001 from 1
formatcustom000 -- Don Guillett SalesAid Software "RLB" wrote in message ... Hi, I table of 3 digit numeric fields, data in field can be 1-3 significant digits. I use the numbers as input to another look up that requires all 3 digits and filled with zeros. So I need a formula to determine number of significant digits in original field and if less than 3 fill to 3 with zeros. I tried "format" as well as "precision as displayed", but niether worked. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill with zero
Are you talking about -
012 OR 120 AND Are you talking about *true* XL numbers or text? For text you might try: =REPT(0,3-LEN(A1))&A1 OR =TEXT(A1,"000") for 012 =A1&REPT(0,3-LEN(A1)) for 120 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RLB" wrote in message ... Hi, I table of 3 digit numeric fields, data in field can be 1-3 significant digits. I use the numbers as input to another look up that requires all 3 digits and filled with zeros. So I need a formula to determine number of significant digits in original field and if less than 3 fill to 3 with zeros. I tried "format" as well as "precision as displayed", but niether worked. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill with zero
Ragdyer
I tried the =rept and it worked. Thanks, RLB "Ragdyer" wrote: Are you talking about - 012 OR 120 AND Are you talking about *true* XL numbers or text? For text you might try: =REPT(0,3-LEN(A1))&A1 OR =TEXT(A1,"000") for 012 =A1&REPT(0,3-LEN(A1)) for 120 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "RLB" wrote in message ... Hi, I table of 3 digit numeric fields, data in field can be 1-3 significant digits. I use the numbers as input to another look up that requires all 3 digits and filled with zeros. So I need a formula to determine number of significant digits in original field and if less than 3 fill to 3 with zeros. I tried "format" as well as "precision as displayed", but niether worked. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Fill with zero
Hi Don,
For some reason format doesn't work on this problem. When I go to format cell it is a "special with chinese" and when I change to custom 000 it won't take. Bob "Don Guillett" wrote: To get 001 from 1 formatcustom000 -- Don Guillett SalesAid Software "RLB" wrote in message ... Hi, I table of 3 digit numeric fields, data in field can be 1-3 significant digits. I use the numbers as input to another look up that requires all 3 digits and filled with zeros. So I need a formula to determine number of significant digits in original field and if less than 3 fill to 3 with zeros. I tried "format" as well as "precision as displayed", but niether worked. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto fill option | New Users to Excel | |||
Fill Effects on Fill Color | Excel Discussion (Misc queries) | |||
Fill handle formula | Excel Worksheet Functions | |||
How to fill colour in Excel, it appers No fill in my computer? | Excel Discussion (Misc queries) | |||
Excel printing problem - won't print cell under a "fill effect" bo | Excel Discussion (Misc queries) |