See all How-To Articles

Pad Numbers (Add Leading Zeros) in Excel & Google Sheets

In this tutorial, you will learn how to pad numbers by adding leading zeros in Excel and Google Sheets.

 

add leading zeroes initial data

 

Pad Numbers by Using Custom Cell Format

The first option to pad numbers is to use a custom cell format. Say you have the following list of codes in Column B and they need to have 10 digits total, with leading zeros.

 

add leading zeroes initial data

 

The value in B2 needs to start with 5 zeros; B3 needs 6 zeros, etc. To achieve this, follow these steps:

1. Select the range with numbers (B2:B7) and right-click anywhere in the selected area, then choose Format Cells…

 

excel format cells

 

2. In the Format Cells window, Number tab, (1) select Custom in the Category list and (2) enter 0000000000 for Type, then (3) click OK. As you can see in the Sample above Type, Excel adds as many zeros as needed until there are 10 digits.

 

excel format cells add leading zeroes

 

Finally, all numbers in the range have a length of 10 with leading zeros.

 

add leading zeroes final data

 

Note that these codes are stored as numerical values and only display leading zeros. This means the numbers can be used in calculations, but the zeros are not part of the value and will disappear if the cell format changes.

Pad Numbers With the TEXT Function

Another option to pad numbers is to use the TEXT Function.

1. Select the adjacent cell (here, C2) and enter this formula:

pad numbers text function

 

2. Drag the formula down the column (through cell C7).

 

pad numbers text function 2

 

Again, all values from Column B have leading zeros. Note that the 10-digit codes are now stored as text, and the leading zeros are a part of the actual value. This means you cannot use these numbers in any calculations, but the characters will be the same regardless of the cell format.

Pad Numbers in Google Sheets

Both custom cell formatting and the TEXT Function can be used in Google Sheets as well. Since the TEXT Function works the same as in Excel, we’ll explain how to pad numbers in Google Sheets using custom formatting.

1. Select the range with numbers (B2:B7) and right-click anywhere in the selected area. In the Menu, go to Format > Number > More Formats > Custom number format.

 

google sheets custom number format

 

2. In the pop-up window, enter 0000000000 and click Apply.

 

google sheets add leading zeroes

 

The result is the same as in Excel: All codes are displayed as 10-digit numbers.

 

google sheets add leading zeroes 2