How to Add Leading Zeroes in Excel – for Numbers?

A. Insert Numbers with Leading Zeroes in Excel

To add leading zeroes in Excel to a number, use one of these methods. But before using one of these methods, you have to decide which category you would like to use.

Numbers change to text format

• Number format gets changed to text format after adding leading zeroes
• Excel mathematical functions will not work for these numbers

Only display format is changed – not the values

• The number format is retained.
• All Excel functions will work.

Here is how to implement these 2 different appraoch.

1. Simeplest method to add leading Zeroes for Numbers in Excel

Follow these steps to quickly add zeroes to start of a number.

1. Double click or press F2 in a Excel cell
2. Add a single quote or appostrophe to start of the number
3. Then add zeroes & numbers like – ‘0000123
4. Press Enter

With this the leadign zeroes will not disappear. But the problem is is we apply formula like SUM, AVERAGE etc., to these numbers, the formula will give zero as answer or return error message.

This wil not create any problem in the vba macro. The functions in macro will perform as usual.

2. Formula: Text Function to prefix zeroes

Lets consider cell A1 has number 123. If you would like to insert leading zeroes to this number and enter the value in cell A2, then try this formula

=Text(A1,”000000″)

This will result in 000123. This fucntion will also convert the number into a string. So any mathematical formulas will not work on these results.

3. Formula: Base Function to insert leading zeroes

With same example as considered in previous section. The formula with base would look like this.

=Base(A1, 10, 4)

This would give output as “0123”. The parameters are – value to be converted, base, number of digits in output.

B. Add leading Zeroes without Converting numbers to text

With this way, you will be able to add leading zeroes to the display format. That means, the Excel cell will display leading zeroes. But if you double click on the cell, it will display only the number, not the leading zeroes.

Follwo these steps to add leading zeroes only to the display format.

1. Click on the cell with the number
2. Right click, Format or Ctrl + 1
3. Click Custom
4. Enter number of zeroes equal to number of digits.
5. Click ok

For example, if cell has value 123 and you would like to have 5 digits in total with 2 leading zeroes. Then enter “00000”.

With this method, you can use any Ecel mathematical formula and they will work as how it should.

Exernal Links: Here is another topic that explains the same technique with few more additional methods.