If the original number contains 2 digits, 3 zeros are added.
If the original number contains 3 digits, 2 zeros are added, and so on.
The best approach depends on your needs.
If numbers really should be text values, use Option 1.
If numbers need to remain numeric values, use Option 2.
Both options are easy to implement, but Option 1 is more flexible.
The TEXT function returns a number formatted as text, using thenumber formatprovided.
Note that Excel willautomaticallyalign text values to the left, but you could manually set alignment as desired.
The key thing to understand with this option is that the numeric values underneath arenot affected.
Applying a number format only changes the way the numbers aredisplayed in Excel.
When you click “OK” the number format is applied and numbers are displayed with leading zeros.
The key difference is that Option 1 results intext, while Option 2 preserves thenumber.
Be aware that you may need to cater to this difference in other formulas.
The formulas below show how this difference affects a VLOOKUP formula:
More on VLOOKUP here.
in a text string with the number format of your choice.
Related videos
How to combine functions in a formula
Shortcuts for paste special
