Fill with zero

[RLB]

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.

[Don Guillett]

To get 001 from 1
format>custom>000

--
Don Guillett
SalesAid Software
[email protected]
"RLB" <[email protected]> wrote in message
news:[email protected]...
> 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.

[Ragdyer]

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" <[email protected]> wrote in message
news:[email protected]...
> 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.

[RLB]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>

[RLB]

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
> format>custom>000
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "RLB" <[email protected]> wrote in message
> news:[email protected]...
> > 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.
>
>
>