Zero-Fill

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hhow do I (left) zero fill a right aligned set of numbers without inserting
each one manually?
 
You can set a custom format, which will DISPLAY the leading zeros, but they
won't really be there. Format->Cells->Number->Custom and use 000000 (for a
total of 6 digits) or 00000000 (for a total of 8 digits).

If you really need to change the VALUE and the DISPLAY, in an empty column
use a formula like =Text(a2,"000000"). This creates a 6 digit, zero-padded
value. You'll have to copy the formula and Edit-Paste Special-Value over the
orgiinal values to convert them to zero-padded values
 
Thank you! What I'm trying to do is rearrange a column of numbers in
ascending order by the 2nd two numbers on the right. (i.e. terminal digit
order). The maximum number is eight, but some are 7 and even 6 digits long.
When I move 'Text to Columns' and indicate 'fixed width', if they are not
zero-filled, they do not right-align. So, in response to your ideas, I guess
yes, I do need to change the actual value because the first idea doesn't
carry the zero's over into the 'Convert Text to Columns Wizard'.
 
Try this:

=A1&REPT(0,8-LEN(A1))


TDL said:
Thank you! What I'm trying to do is rearrange a column of numbers in
ascending order by the 2nd two numbers on the right. (i.e. terminal digit
order). The maximum number is eight, but some are 7 and even 6 digits long.
When I move 'Text to Columns' and indicate 'fixed width', if they are not
zero-filled, they do not right-align. So, in response to your ideas, I guess
yes, I do need to change the actual value because the first idea doesn't
carry the zero's over into the 'Convert Text to Columns Wizard'.
Click to expand...
 
In an adjacent column, use

=right(cell with value,2)

then sort on the column with these formulas
 
Thank you again. You are too kind. I'm somewhat new at this, can you be
more specific? I have a column of ID numbers cells A1 to A33 which are right
justified. I need to zero fill to the left so each ID number equals 8 digits
long (some are only 6 & 7 digits) so that I can move Data,'text to column'
and separate them (fixed width) so I can sort them by the 2nd two right
digits, then the 4th & 5th digits, then the remaining digits. The furthest
right digit is a 'check' digit.
 
I'll assume that "sort them by the 2nd two right digits" means sorting by
the 6th and 7th digits from the left.

Insert 3 columns to the left of you data (Your data will then be in col D)

In new col A row 1 use this formula to extract the 6th & 7th digits

=MID(TEXT(D1,"00000000"),6,2)

In new col B row 1 use the formula to extract the 5th & 5th digits

=MID(TEXT(D1,"00000000"),4,2)

Finally, in cell C1 use the formula to get the first 3 digits

=LEFT(TEXT(D1,"00000000"),3)

Copy those formulas down, then you can sort the whole mess on the 1st 3
columns
 
Thank you so much. This is a much more efficient way to sort these.
Thank you, thank you!!
Enjoy your weekend, wherever you are.
 
I found it most easiest creating my own custom format to solve this.

1. Right click the cell range you want to format
2. In the tab 'Number' chose "Custom"
3. In the field 'Type' enter zeroes. Enter as many
zeroes as the preferred length of your numbers.
( e.g. for 10 digits enter "0000000000" )
 
Hi,

The command it

Format, Cells, Number tab, Custom and in the Type box on the right enter
the format codes you want to use. Look in the help system for a list of the
codes and examples under the topic Custom Formats.

If this helps, please click the Yes button,

Cheers,
Shane Devenshire
 
Back
Top