Keep Leading Zeros in Numbers in Excel - 2 Ways

Add to Favorites
Author: | Edits: don

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.

3da56b1553829346994abb2d82ed17df.png

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

872eaa09a1d2f83ce60866b29fc15ecc.png

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):

e01df5c690213ac268096415a2610e52.png

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

f9fc73a0f36c16aa34e2a2f7265ce77e.jpg

Then, add the number:

441ec9f537b5ee9276fa9460ebec67da.png

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.


Downloadable Files: Excel File

Question? Ask it in our Excel Forum


Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons 50+ Hours of Instruction 200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course