Add leading zeros to numbers

In this example, the goal is to add leading zeros to a given number so that the total number of characters displayed is 5. Sometimes this is referred to as "padding" a number with zeros, because the number of zeros needed is variable. 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.

There are two basic ways to solve this problem: (1) convert the number to text with leading zeros (2) apply a custom number format to display the number with leading zeros. 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.

Option 1 - convert to text

A simple way to add leading zeros to a number is to use the TEXT function. The TEXT function returns a number formatted as text, using the number format provided. In this case, we want the final result to have five characters total, so the number format includes five zeros: "00000". This number format is used directly in the TEXT function as the format_text argument:

=TEXT(B5,"00000") // returns "00127"

The result is the text string "00127". Note that Excel will automatically align text values to the left, but you can manually set alignment as desired. To replace the original numbers with the converted text values: first copy the converted values, then select the original numbers and use Paste Special > Values to overwrite the original numbers.

Related video: How to do in place changes with Paste Special

Option 2 - apply number format

Another way to add leading zeros to a number is to apply a custom number format to display the numbers with leading zeros. The key thing to understand with this option is that the numeric values underneath are not affected. Applying a number format only changes the way the numbers are displayed in Excel.

To apply a number format to display leading zeros, select the values in F5:F16 and use the shortcut Control + 1 to display the Format Cells window:

Then navigate to the Number tab, select "Custom" and enter 00000 in the "Type" input area:

Note the Sample area will show an example of the format applied to the first cell in the selection. When you click "OK" the number format is applied and numbers are displayed with leading zeros. Note the formula bar will continue to show the numeric value in its original form:

Summary

Both options above work well for adding leading zeros to a number. The key difference is that Option 1 results in text, while Option 2 preserves the number. Be aware that you may need to cater to this difference in other formulas. For example, if you are performing a lookup operation with VLOOKUP or INDEX and MATCH, you must match the lookup value to the data. The formulas below show how this difference affects a VLOOKUP formula:

=VLOOKUP("00127",range,column,FALSE) // look up text
=VLOOKUP(127,range,column,FALSE) // look up number

More on VLOOKUP here.