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:
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:
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.
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
The RIGHT function extracts characters beginning from the rightmost side to the left. The syntax only has two arguments and looks like this:
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:
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])
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:
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:
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:
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.
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?
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.