Keep Leading Zeros in Numbers in Excel - 2 Ways

I'll show you 2 ways to add and keep leading zeros in front of numbers in Excel.

These two methods are very simple to use and will help keep your data from getting corrupted.

Method 1 - Add Apostrophe

The first way to keep leading zeros in front of numbers is to put an apostrophe ' in front of the number.

Notice how I input '0123 and not just 0123.  Now, when I hit Enter, the number will keep its leading zero.

In the cell, all we see is the number; the apostrophe has become invisible.  But, if you look to the Formula Bar, you can still see the apostrophe and this is the same if we edit the cell, the apostrophe will still be in there.

This is a very simple and easy to use trick to get the number to keep its leading zeros.

Method 2 - Formatting

The second method is to format the cell as Text before you put the number with leading zeros into it.

If you are importing data, make sure to designate a column of numbers that contain leading zeros, often times this will be some sort of part number or code, as Text instead of numbers.

If I simply input the number 0123 into a cell and then hit enter, it will convert the cell to a number format and look like this (cell A3):

Now, let's first format a cell as Text:

Then, add the number:

This time, cell B3, being formatted as Text, keeps the leading zero.

Notes

Often, you will run into this problem when importing large sets of data into Excel and the second method will work better when you are doing that.

If you are typing data into Excel, the first method is often easier to use.

Download the sample workbook to see the above examples in action.