Excel has a habit of removing zeroes if they are the first digits in numbers. In some cases that isn’t an issue, but in the case of certain types of data, like zip codes, you might need to find a way to add leading zeroes in Excel so that the numbers are correct.
Fortunately you can do this with the help of a formula, similar to this subtraction formula, so there is only a small amount of manual entry that you will need to do in order to add these leading zeroes to your cells.
How to Use the TEXT Function to Add Zeroes in Front of Numbers in Excel
The steps below will assume that you have a column of numbers in Excel 2013, and that you would like all of them to have the same number of characters. This is common with United States zip codes, as there are several zip codes that start with the number zero. If you only want to display your numbers with the same number of digits, and don’t wish to actually change the value in the cell, then we will show you a quick formatting change that you can make at the end of this article.
You may also want to look into using concatenate in Excel if you have data in multiple columns that needs to be combined.
Step 1: Open your spreadsheet in Excel 2013.
Step 2: Click inside an empty cell in the spreadsheet.
Step 3: Type the formula =TEXT(XX, “00000”) but replace XX with the location of the cell that you wish to modify, and replace 00000 with the number of characters that you want in the cell. Press Enter on your keyboard after you have finished entering the formula.
Step 4: Click the handle at the bottom-right corner of the cell containing the formula, then drag the handle downward to copy the formula for the rest of the cells in that column. The formula will automatically adjust to use the cell location relative to your original formula.
If you would rather change the format of your cells to display your values with leading zeroes, then you can do so by selecting the cell(s) that you wish to modify, right-clicking one of the cells, then choosing Format Cells.
Select the Custom option from the list at the left of the window, then click inside the field under Type: and enter a number of zeroes equal to the number of digits you want to display in the cell. Click the OK button at the bottom of the window when you are done.
As mentioned earlier, this formatting option will display your cells with the leading zeroes, but it will not change the values to include those leading zeroes.
Do you have columns containing separate bits of information, and you would like to combine them into one cell? Learn how to easily combine multiple cells in to one in Excel with the help of the concatenate formula.
Matthew Burleigh has been writing tech tutorials since 2008. His writing has appeared on dozens of different websites and been read over 50 million times.
After receiving his Bachelor’s and Master’s degrees in Computer Science he spent several years working in IT management for small businesses. However, he now works full time writing content online and creating websites.
His main writing topics include iPhones, Microsoft Office, Google Apps, Android, and Photoshop, but he has also written about many other tech topics as well.
Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.