How to add zero in front of number in Excel [9 methods]

January 30, 2022June 1, 2022

By default, Microsoft Excel automatically removes leading zeros from the beginning of numbers. That’s because, from a mathematical point of view, a zero before an actual number is irrelevant. However, there are cases when adding a zero in front of a number is important. Think of postal codes, phone numbers, or account numbers. A lot of them start with zero. In this tutorial, you will learn to add zero in front of a number in Excel using different methods.

Before we begin, I recommend that you download the Excel file that contains all the formulas presented in this tutorial.

Convert numbers using the apostrophe (‘)

The easiest way to add leading zeros in Excel is to add an apostrophe (‘) before the numbers.

This automatically converts numbers into texts, so you will see a green triangle at the top-left corner of the cell indicating an error. When you click the warning sign beside the cell, the number stored as text error will be highlighted. Click on the warning sign and select “Ignore Error” from the options to remove the error message.

Note: This method works with one cell at a time. If you need to add leading zeros to multiple cells, I recommend using one of the following methods.

Changing the Cell Formatting 

Using the Text Format

The quick way would be to change the cell formatting to Text so that Excel doesn’t trim your leading zeros by default. To do this, follow the steps:

1. Right-click on the cell or range of cells where you want to add zero in front of your numbers.

2. Select Format cells.

3. The Format Cells dialog box will appear. Select Text from the category list.

4. Click OK.

Alternatively, you can select the range of cells, go to the Home tab, and select Text from the drop-down menu located in the Number section.

Note: This method can be applied to a range of cells, but the data will appear as text values instead of numbers.

Using a Custom Format

You can apply custom formatting if you don’t want the data to appear as text and keep the number value. To do this, follow the instructions below:

1. Right-click on the cell or range of cells where you want to add a zero.

2. Choose Format cells.

3. The Format Cells dialog box will appear. Select Custom from the category list to use a new custom format.

4. Enter zeros in the Type box. The Sample box above will show a preview of how the value will appear in the cell.

5. Click OK.

Note: The underlying data will remain stored as a number and display the added leading zeros using the custom number formatting.

Add leading zeros in Excel using formulas

You can use different functions to add leading zeros in front of numbers. These methods work for single cells or multiple cells. Just drag the fill handle down to apply the formula to all the cells where you want to add the zeros.

How to add zero in front of a number using the TEXT function

The Text function converts numeric values into text. The syntax is:

=Text(value, format_text)

where

• value – the value or cell to be converted
• format_text – the format to be used 

To add zeros using this function, type TEXT after the equals sign. Inside the parentheses, enter the cell you want to convert and type as many zeros as needed, separated by a comma. The zeros must appear in double quotation marks. In the example below, I’ve added zeros to obtain a six-digit value:

=TEXT(C2, "000000")

Press Enter to finish the formula and apply the text formatting in the selected cell.

How to add zero in front of a number using the REPT function

The REPT function repeats characters a given number of times. The name is short for Repeat. This formula works by combining the number of leading zeros with the cell values.

=REPT(text, number_times)

where

• text – the text value to repeat
• number_times – the number of times to repeat the text value

To add zeros in front of a number using REPT:

1. Type =REPT(

2. Inside the parentheses, enter zero followed by a comma.

3. Then, enter the number_times argument, which will dictate how many zeros to add. In the sample above, I’ve added two leading zeros.

4. Close the parentheses and then type the ampersand (&) symbol

5. Select the cell where your number is stored, and press Enter to apply the formula.

The final formula will look like this =REPT(0, 2)&C2

How to add zero in front of a number using the RIGHT function

The RIGHT function extracts characters beginning from the rightmost side to the left. The syntax only has two arguments and looks like this:

=RIGHT(text, [num_chars])

where

• text – the text you want to extract characters from
• num_chars – the number of characters to extract from the text

This method allows you to add zeros based on the length of your number. For example, if you are looking to always display six-digit numbers and add 0 in front of the number if it’s shorter, you can use the following formula:

=RIGHT("000000"&C2, 6)

This will create a default number format. For example, if your number is 1111, the formula will return 001111. However, if your number is 111111, the formula will return 111111 without adding leading zeros.

Note: remember that the RIGHT function only displays the number of characters set by the num_chars argument. If your number is higher than that, it will be trimmed.

How to add zero in front of a number using the BASE Function

The BASE function converts a number into a text representation with the given base. The function takes three arguments, and the syntax is the following:

=BASE(number, radix, [min_length])

where

• number – the value that you want to convert
• radix – the base that is used in the traditional numbering system. For Example, 2 stands for binary, 10 stands for decimal.
• min_length – This is used to determine the minimum string length of the output

To add zeros using this function:

1. Type =BASE(

2. Select the cell where the original number is stored and add a comma

3. Next, enter 10 as the base (we want the same type of number) followed by a comma

4. Lastly, enter the string length. I’ve used 6 in my example, which will return a number with a minimum of 6 digits.

5. Type ) to close the parentheses and press Enter to apply the formula.

The final formula I got is: =BASE(C2, 10, 6)

How to add zero in front of a number using the CONCATENATE function

The CONCATENATE function allows you to join multiple strings or numbers together. The syntax is straightforward:

=CONCATENATE(text1, [text2]..)

where

• text1 – the first item to be joined
• text2 – the second item to be joined
• textn – the Nth item to be joined

Now let’s look at an example. Let’s add two zeros in front of an existing number. To do this:

1. Type =CONCATENATE(

2. Then, type “00” followed by a comma

3. Select the cell that contains the original number

4. Type ) to finish the formula and press Enter

Here’s the formula I used: =CONCATENATE("00", C2)

Note: Starting with Excel 2019, you can also use the CONCAT function to achieve the same result. The CONCAT function is also available in Excel for Microsoft 365.

Add leading zeros using the Ampersand (&) symbol

The ampersand (&) symbol allows you to combine values from cells or strings similar to the CONCATENATE function.

The steps to create leading zeros using this method are:

1. Type = followed by the number of zeros inside double quotation marks like this =”00″

2. Type the ampersand (&) symbol

3. Select the cell where your original number is stored

4. Press Enter to finish the formula.

My result is: ="00"&C2

What to do next?

In this article, I’ve shown you how to add zero in front of number in Excel using 9 methods. If you struggle with any of the formulas, please leave a comment, and I’ll help you out.

I also encourage you to subscribe to the newsletter using the below form. I send weekly tips and practical examples to improve your Excel skills.