HOW TO prefix zeroes before a variable length number in Excel
Excel usually eats up any zeroes prefixed to a number that you type. So what if you explicitly needed them?
For a fixed length number, you have to use the option Format Cells after right-clicking the cell or range that you want to impose a number format upon. In the dialog box that opens go to to the Number tab, pick the Custom option & specify the number format. If you wanted the number 8217 or any 4 digit number to have 5 zeroes prefixed before it, you would have to specify the number format as 000000000 so that it shows up as 000008217. The trick here is to know what the meta-characters in the format mean.
Browsing through the generally good documentation by F1-ing, I did not find a format to handle variable length numbers for the scenario just described. I got a solution from a MS Knowledgebase article, which is to use the format "00000"#. The double quotes in the number format helps us prefix the desired number of zeroes before a variable length number.
Related:
Excel Tips & Tricks
For a fixed length number, you have to use the option Format Cells after right-clicking the cell or range that you want to impose a number format upon. In the dialog box that opens go to to the Number tab, pick the Custom option & specify the number format. If you wanted the number 8217 or any 4 digit number to have 5 zeroes prefixed before it, you would have to specify the number format as 000000000 so that it shows up as 000008217. The trick here is to know what the meta-characters in the format mean.
Browsing through the generally good documentation by F1-ing, I did not find a format to handle variable length numbers for the scenario just described. I got a solution from a MS Knowledgebase article, which is to use the format "00000"#. The double quotes in the number format helps us prefix the desired number of zeroes before a variable length number.
Related:
Excel Tips & Tricks
Comments
Post a Comment