How to Add Leading Zeroes to Numbers in Excel 2013

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.

select the cell for the fixed data

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.

how to add leading zeroes in excel 2013

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.

how to put zeroes in front of numbers in excel 2013

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.

how to format cells in excel 2013

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.

how to apply custom formatting to cells in excel 2013

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. newsletter

Disclaimer: Most of the pages on the internet include affiliate links, including some on this site.