Exceljet

Quick, clean, and to the point

Add leading zeros to numbers

Excel formula: Add leading zeros to numbers
Generic formula 
=TEXT(A1,"00000")
Summary 

To add leading zeros to a number, you can take one of two basic approaches: (1) convert the number to a text value with leading zeros (2) apply a number format to display the number with leading zeros. Both approaches are described below. In the example shown, the formula in D5 uses Option 1 to convert B5 to text:

=TEXT(B5,"00000")

The result in column D is text – the number is embedded in a text string with the correct number of zeros. Read below for details and the steps needed to apply Option 2.

Explanation 

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:

Format Cells dialog box

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

Select Number > Custom

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:

Numeric values unchanged by number format

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.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.