Cell format with leading zeros

  • Thread starter Thread starter Amber
  • Start date Start date
A

Amber

Say I have cells with these values:
1
12
123
and I want them all to have a minimum of 3 positions with leading zeros if
needed like this:
001
012
123
How can I do that?
 
Bingo! Does exactly what I want. Thanks.
That lead me to another question:
Suppose I had a limit of 3 characters and one cell had a value of 1234 and
wanted to round to 123?
How could I format the whole column so that 1 would look like 001 but 1234
would look like 123?
 
Amber wrote......
Bingo! Does exactly what I want. Thanks.
That lead me to another question:
Suppose I had a limit of 3 characters and one cell had a value of 1234 and
wanted to round to 123?
How could I format the whole column so that 1 would look like 001 but 1234
would look like 123?
Click to expand...

Amber,

Try the following steps...

Steps

1) Create a helper column (an additional column)
2) Format Helper column as per Ken's instructions
3) Put in a formula in the form =IF(LEN(A1)>3, A1/((LEN(A1)-3)*10),A1)
4) Copy down the column (you now have the solution)

If desired, you can overwrite the original column

5) Copy helper column
6) Select original column
7) Edit | Paste Special | Values

Please note, if you have values with decimals, this solution WILL NOT work.
That is 123.456 will not work.

If your numbers are other than the form 1234, please let us know.

Best regards,
Kevin
 
Whoops, change equation to

3) Put in a formula in the form =IF(LEN(A1)>3, A1/(10^(LEN(A1)-3)),A1)

Sorry,

Kevin
 
Back
Top