How to add 0 in front of a Number in Excel

Are you struggling to add 0 in front of a number in Excel, only to see it disappear? This is a common problem that many Excel users face. In this blog post, I will show you several quick and easy ways to keep those leading zeros in Excel.

This guide explains how to keep leading zeros in Excel for new and existing data. By the end, you’ll know how to manage leading zeros efficiently.

Please note that methods 1 to 5 convert numbers to text, which may prevent them from being used in calculations. These methods are ideal for zip codes, postcodes, mobile numbers, and part numbers.

Method 6, on the other hand, retains the number as a value, allowing it to be used in calculations. This method is handy for any scenario where formulas are necessary.

Let’s dive into the different techniques you can use to add a zero in front of a number in Excel and keep it there.

Excel Practice Files

Why Does the 0 Disappear In Excel?

If a cell is formatted as a number, Excel considers leading zeros insignificant and drops them. 

This can be frustrating when entering part numbers, postcodes, item codes, and mobile numbers requiring a zero before a number.

It doesn’t matter how often you try to enter 0; you still end up with the same result: Excel removes the zeros! For a deeper understanding, visit my blog ‘Why does the 0 disappear?’

So here’s how you can make sure the 0’s are included.

1. Use An Apostrophe To Add 0 In Front Of A Number

Add 0 in front of number while typing into a cell

One way to add 0 in front of a number in Excel is to use an apostrophe before the number. For example, if you type ‘090, Excel will treat it as text and keep the leading zero. This method is simple and effective for entering data.

Step-by-Step Instructions:

Step 1: Click on the cell where you want to enter the number with leading zeros.

Step 2: Type an apostrophe (‘) before the number.

Step 3: Enter the number with as many leading zeros as required (e.g.,’090). In the example below, only one zero has been added in front of the number.

Step 4: Press Enter. Excel will display the number with the leading zero [1].

2. Change Number Format To Add 0 In Front Of A Number In Excel

Automatically Add Leading Zeros As You Enter Numbers

Another method to add 0 in front of a number in Excel is to change the number format to text. Use this option when you have a range of cells where leading zeros will be entered. This option allows you to start entering zeros, but you don’t need to insert the apostrophe in as the cells are already seen as text.

Step-by-Step Instructions:

Step 1: Highlight the cells where you want to add leading zeros [1].

Step 2: Go to the “Home” tab on the ribbon.

Step 3: In the “Number” group, click on the dropdown arrow next to the number format box.

Step 4: Select “Text” from the list.

Step 5: Now, you can enter numbers with leading zeros into the Text formatted cells without using an apostrophe.

Note: This method will change the numbers to text-formatted numbers, which may prevent them from being used in calculations.

3. Use The TEXT Function To Add 0 In Front Of A Number

Add zero in front of the number while keeping a fixed number of digits

You can use the TEXT function if you have an existing list and want to add leading zeros. For example, =TEXT(A5, “0000”) will format the number in cell A1 with four digits, adding leading zeros as needed.

Step-by-Step Instructions:

Step 1: Click on the cell where you want the formatted number to appear.

Step 2: Type the formula =TEXT(A5, “0000”), replacing A5 with the reference to the cell containing the original number.

Step 3: Press Enter. The cell will display the number with leading zeros.

Note: This method will change the numbers to text-formatted numbers, which may prevent them from being used in calculations.

4. Use the CONCAT Function To Add 0 In Front Of A Number

Add a Fixed Number of Zeros In Front Of the Number

The CONCAT function (or the older CONCATENATE function) allows you to add 0 before a number in Excel. For example, =CONCAT(“0000”, A5) will add four leading zeros to the number in cell A1.

Step-by-Step Instructions:

Step 1: Click on the cell where you want the formatted number to appear.

Step 2: Type the formula =CONCAT(“0000”, A5), replacing A5 with the reference to the cell containing the original number.

Step 3: Press Enter. The cell will display the number with leading zeros.

5. Use A String Formula To Add 0 In Front Of A Number

Add a Fixed Number of Zeros In Front Of the Number

Using the ampersand (&) operator is another way to add 0 in front of a number in Excel. Advanced Excel users often use this to create string formulas. For example, if you want to add leading zeros to the number in cell A8, you can use =”0000″&A8. This appends four leading zeros to the number. Here’s how you can do it:

Step-by-Step Instructions:

Step 1: Click on the cell where you want the formatted number to appear.

Step 2: Start with the equals sign (=).

Step 3: Type the leading zeros inside double quotation marks (“).

Step 4: Add the ampersand symbol (&) after the quotation marks.

Step 5: Enter the reference to the cell containing the original number (e.g., A8).

Step 6: Press ENTER to apply the formula and display the number with leading zeros.

Note: These methods will change the numbers to text-formatted numbers, which may prevent them from being used in calculations.

6. Add Zeros In Front Of A Number Using A Custom Number Format

Add Leading Zeros While The Number Remains Fully Functional For Calculations

You can also use custom number formatting to add 0 in front of a number in Excel. This allows numbers to display with leading zeros while remaining fully functional for calculations.

For example, a manufacturing company may assign 6-digit batch numbers and need future batches to follow this pattern while keeping the leading zeros. If the first batch number is 000110 and entered in cell A2, a simple formula like =A2+1 ensures the sequence continues (e.g., 000110, 000111, 000112). By applying a custom format of 000000, Excel displays the numbers correctly while treating them as numeric values, allowing seamless calculations and sorting.

Step-by-Step Instructions:

Step 1: Highlight the cells where you want to add leading zeros.

Step 2: Right-click and select “Format Cells.”

Step 3: In the “Format Cells” dialogue box, go to the “Number” tab.

Step 4: Select “Custom” from the list on the left.

Step 5: Enter the desired format in the “Type:” box (e.g., 000000).

Step 6: Click “OK.”

Step 7: The cells will display the numbers with leading zeros and retain them as values, allowing you to use them in calculations.

Note: Using this method will change the look of your number, but leave your number format as a number. This is extremely helpful if you are trying to avoid #VALUE errors.

Sorting Numbers Formatted with Leading Zeros

The above methods to add 0 in front of a number in Excel are beneficial when dealing with text or numerical data.

However, when you sort your data, if the column you want to sort contains numbers stored as numbers (using method 6) and numbers stored as text (using methods 1 to 5), you need to format them all as either numbers or text. If you do not apply this format, the numbers stored as numbers are sorted before those stored as text.

Conclusion

By mastering these techniques for adding 0 in front of a number in Excel, you enhance the clarity and accuracy of your data presentation. Add these tools to your ‘Excel toolbox’ to save time and work more efficiently.

Excel Practice File Download