January 30, 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.

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.

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.

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.

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.

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.

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`

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.

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

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.

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`

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.

About me

My name is Radu Meghes, and I'm the owner of excelexplained.com. Over the past 15+ years, I have been using Microsoft Excel in my day-to-day job. I’ve worked as an investment and business analyst, and Excel has always been my most powerful weapon. Its flexibility and complexity make it a highly demanded skill for finance employees. I launched excelexplained.com back in 2017, and it has become a trusted source for Excel tutorials for hundreds of thousands of people each year.

If you'd like to get in touch, you can contact me on LinkedIn.

Thousands of people have benefited from my free Excel lessons. My only question is, will you be next?

I have compiled a list of the best Excel online courses. It covers basic and advanced topics, VBA, and Power BI.

View coursesExcel is a very complex application, and understanding it on your own may prove difficult and time-consuming. I have created ExcelExplained.com to help you better understand each function and provide useful examples for different tasks. If you can’t find the answer to your question in one of the published articles, please leave a comment or send me an email, and I will do my best to help you out.

Affiliate Disclosure: When you purchase a product through a link on our website, I may earn an affiliate commission. There are no additional costs to you.

Copyright © 2017-2022 ExcelExplained.com