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

Comments

Popular posts from this blog

Maven Crash Course - Learn Power Query, Power Pivot & DAX in 15 Minutes

"Data Prep & Exploratory Data Analysis" course by Maven Analytics

Oracle Cloud Infrastructure 2024 Generative AI Professional Course & Certification Exam (1Z0-1127-24)