Zero-Fill
Hhow do I (left) zero fill a right aligned set of numbers without inserting
each one manually? |
Zero-Fill
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? |
Zero-Fill
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? |
Zero-Fill
Try this:
=A1&REPT(0,8-LEN(A1)) "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? |
Zero-Fill
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? |
Zero-Fill
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? |
Zero-Fill
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 "TDL" wrote: 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 , 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? |
Zero-Fill
Thank you so much. This is a much more efficient way to sort these.
Thank you, thank you!! Enjoy your weekend, wherever you are. "Duke Carey" wrote: 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 "TDL" wrote: 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 , 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? |
Create your custom format
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" ) |
Create your custom format
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 "unknown" wrote: |
All times are GMT +1. The time now is 03:15 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com