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.

"Duke Carey" wrote:

In an adjacent column, use

=right(cell with value,2)

then sort on the column with these formulas



"TDL" wrote:

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'.

"Duke Carey" wrote:

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

"TDL" wrote:

Hhow do I (left) zero fill a right aligned set of numbers without inserting
each one manually?