We usually need to enter zeros before phone numbers, zip codes, product codes, and account numbers.
Even if we enter leading zeros when we type in Excel, all leading zeros immediately vanish from our Excel file once we press the enter key. To keep the leading zeros of a number, we need to do something different.
In this article, we will look at various strategies for adding leading zeros in Excel.
Method 1 – Using an Apostrophe to Add Leading Zeros in Excel
The easiest method to add leading zeros in Excel is to use an apostrophe before the number.
The below table contains the last 4 digits of new product codes.
Now, we have to format these product codes as seven-digit numbers by adding leading zeros in order to enter them into the accounting system.
We can simply add an apostrophe before entering three leading zeros as in the image given below.
‘000
We are not required to type each product with the apostrophe and three leading zeros.
First, type the apostrophe, three leading zeros, and the last four digits of the product code.
Go to the next cell and press ‘Control + E’. Excel autofill function will fill all the product codes with the leading zeros.
It is important to note that after we use the apostrophe, the number will be saved in text format. So, if we select all system codes, we will see only the count in the status bar.
We cannot use the above numbers with the apostrophe as numbers in functions.
For example, if we enter the SUM function and select cells B3 to B6 as a reference, our answer will be zero. However, we can use those numbers with the apostrophe as numbers for formulas. That means if we add B3+B4+B5+B6, we will get the total value of those 4 numbers.
Also read: How to Remove Leading Zeros in Excel?
Method 2 – Converting the Format to Text to Add Leading Zeros in Excel
Another easy way to add leading zeros in Excel is to convert the number format to text.
Then, we don’t need to add apostrophes before zeros to keep the leading zeros.
The last four digits of new product codes are shown in the table below.
Now, in order to enter these product codes into the accounting system, we have to format them as seven-digit numbers by adding leading zeros.
We can follow the below steps to add leading zeros by changing the number format.
- Select cell B3 and expand the number format options of the Home Tab.
- Select “Text” as the number format.
- Type the last 4 digits of the product code with the leading zeros
- Go to the cell below and press “Control +E” and Excel will create all the product codes with leading zeros.
In this method, the number is stored as Text. So, if we select all system codes, we will see only the count in the status bar.
We cannot use text format cells as numbers in functions.
For example, if we enter the SUM function and select cells B3 to B6 as a reference, our answer will be zero. However, we can use text format cells as numbers for formulas. That means if we add B3+B4+B5+B6, we will get the total value of those 4 numbers.
Also read: How to Add Zero In Front of Number in Excel?
Method 3 – Using Custom Number Formatting to Add Leading Zeros in Excel
Sometimes we have to add leading zeros in the same cell without moving to another cell.
We can quickly add leading zeros by using custom number formatting.
The final four digits of new product codes are listed in the table below.
In order to enter these product codes into the accounting system, we have to now format them as seven-digit numbers by adding leading zeros.
To add leading zeros to the product codes shown above, simply follow the steps below.
- Select all the product codes and press ‘Control +1’ to open the format cells dialog box.
- Select “Custom” from the category.
- Enter seven zeros in the “Type” field, as we need seven-digit numbers.
- Click OK.
Now, all product codes are changed to seven-digit numbers by adding leading zeros.
Unlike the above 2 methods, the numbers are stored as numbers even after the cell formatting.
So, if we select product codes even after we do the cell formatting, the status bar will show the Sum, average, and count for the selected cells.
We can use custom format cells as numbers in functions and formulas as well.
Also read: How to Apply Accounting Number Format in Excel
Method 4 – Using TEXT function to Add Leading Zeros in Excel
We can use the TEXT function to add leading zeros in Excel. This is a dynamic formula.
When you change the original number, Excel will automatically update the numbers with the leading zeros.
The table below shows the last four digits of new product codes.
These product codes need to format as seven-digit numbers by adding leading zeros in order to be entered into the accounting system.
We can use the below TEXT function to add leading zeros to our product codes.
=TEXT(A3,"0000000")
The TEXT function helps to convert a number to text by applying a format code to that number.
The syntax of the TEXT function is TEXT(value,format_text).
The first argument of the TEXT function is the value. We have to select the number that we want to convert. In this case, it is cell A3.
The next argument is the “Format_text”. Here we have to give the format code that we need to apply to the selected number. Since we need seven-digit numbers with leading zeros, we have to enter seven zeros within quotes (“0000000”) for this argument. If you need five-digit numbers with leading zeros, we have to enter five zeros within quotes.
Using the format code “0000000” means that the value in the cell would always be seven digits long. So if the actual number is less than seven digits, leading zeros would be added to ensure it becomes a seven digit number
In this method, the result of the function is stored as Text. So, if we select all system codes, we will see only the count in the status bar.
Also read: Replace Zero with Dash in Excel?
Method 5 – Using REPT and LEN Functions to Add Leading Zeros in Excel
Sometimes we have to add leading zeros to alphanumeric data.
If we use the Excel TEXT function to add leading zeros in such cases, we will not get the expected results. The reason is that we can use only numeric values for the first argument of the TEXT function.
In such a situation, we can create a dynamic formula using the Excel REPT function and LEN function.
The below table contains the last 5 characters of new product codes.
Now, we have to format these product codes that have seven characters by adding leading zeros in order to enter them into the accounting system.
We can use the below formula to add leading zeros to our product codes.
=REPT(0,7-LEN(A3))&A3
The REPT function helps to repeat text a specified number of times.
The syntax of the REPT function is
REPT(text, number_times).
The first argument is text, and we need to enter the text we need to repeat. In this case, we need to repeat zero. So, we enter 0 for the first argument.
Then, we have to enter the number of times to repeat the specified text. We need to create codes with seven characters. So, we have to deduct the number of characters in the original number from 7.
We can use the LEN function to count the number of characters of the original code. The syntax of the LEN function is LEN(text). We can select the original code as the argument for the LEN function. So, we select cell A3 for the LEN function argument.
The result of the REPT function is two 0s.
Next, we can use an ampersand sign and combine the original code. The final result of the formula is product codes with seven characters that have leading zeros.
The result of the REPT function is always text, even if you select a numeric value for the first argument.
Also read: Add Sequential Numbering in Excel
Method 6 – Using the RIGHT Function to Add Leading Zeros in Excel
Another easy way to add leading zeros to alphanumeric or numeric cells is to use the Excel RIGHT function.
The below table contains the last 5 characters of new product codes.
Now, we have to format these product codes that have seven characters by adding leading zeros in order to enter them into the accounting system.
We can use the RIGHT function to add leading zeros to our product codes.
=RIGHT("0000000"&A3,7)
The RIGHT function returns the number of characters we specify from the end of a text string.
The syntax of the RIGHT function is RIGHT(text,[num_chars]).
The first argument is the text. First, you have to add the maximum number of zeros within quotes based on the maximum number of characters of the expected result.
In this case, we need product codes with seven characters. So, we will enter seven 0s within quotes (“0000000”). Then, use an ampersand sign (&) and select the original text). Here, we have selected cell A3 as the original text.
The next argument of the RIGHT function is the number of characters. We need a maximum of 7 characters for a product code. So, we enter 7 for the last argument of the RIGHT function.
The result of the RIGHT function is always text, even if you select a numeric value for the original text of the first argument.
Also read: How to Add Text to the Beginning or End of All Cells in Excel
Some Common FAQs about Adding Leading Zeros in Excel
Why does Excel remove leading zeros by default?
Excel automatically formats numbers and removes leading zeros because it treats them as insignificant in calculations. Excel is designed to work efficiently with numbers, and removing leading zeros is part of its default behavior.
Can I apply the method shown in this article to multiple cells at once?
Yes, you can apply most of these methods to a range of cells simultaneously. For example, you can change the format of multiple cells to text, apply a custom number format to a range of cells at once, or use the formula for an entire column at once.
How do I retain leading zeros when exporting data from Excel to other file formats (e.g., CSV)?
To retain leading zeros when exporting data, ensure that the cells containing the numbers with leading zeros are formatted as text before exporting. This will help preserve the leading zeros in the exported file.
Can I use these methods for adding leading zeros to dates or times in Excel?
While some of these methods might work for dates or times, it is generally better to use custom date or time formatting options available in Excel. This will ensure that Excel still recognizes the cell content as date or time values.
Do these methods for adding leading zeros affect calculations in Excel?
Some methods, such as using an apostrophe or converting the format to text, will cause the numbers to be treated as text, which can impact calculations. However, methods like custom number formatting will not affect calculations, as the numbers remain stored as numeric values.
Can I apply conditional formatting to cells with leading zeros?
Yes, you can apply conditional formatting to cells with leading zeros. Conditional formatting works with the underlying values in the cells, so it will still function as expected, even if you’ve used custom number formatting or other methods to display leading zeros. In case you have added a leading zero by adding an apostrophe or by using the text functions (such as RIGHT), the underlying value in the cell changes, and you will have to adjust the conditional formatting rules accordingly.
Will these methods for adding leading zeros work in other spreadsheet software like Google Sheets or LibreOffice Calc?
Some methods, such as using an apostrophe or changing the format to text, are universal and will work across different spreadsheet software. However, certain methods, like custom number formatting or using specific Excel functions, may have different steps or syntax in other spreadsheet software. It’s important to consult the documentation for the specific software you are using to ensure compatibility.
Can I combine these methods to achieve different formatting requirements?
Yes, you can combine these methods or adapt them to fit specific formatting needs. For example, you can use the TEXT function to create a custom format that includes both leading zeros and other formatting elements like dashes or parentheses.
Is it possible to add a variable number of leading zeros depending on the length of the value in the cell?
Yes, you can use a combination of functions like REPT and LEN to dynamically add leading zeros based on the length of the cell value. This will ensure the total length of the resulting. You can also use the custom number formating to specify the length of the number, which would add leading zeros accordingly based on the length of the value in the cell
In this article, I showed you six different ways to add leading zeros in Excel. You can choose the method that works well for your situation.
I hope you find this tutorial useful. Kindly let me know your thoughts in the comments section below.
Other Excel articles you may also like:
