Keep Leading Zeros

When you copy data to your workbook you may find that when a number has zeros at the beginning, Excel will strip these zeros and leave the rest of the number.

For example, the following data




will be formatted as

By default, Excel applies the General type to all cells and guesses which data type applies to different cells.

As a consequence, it treats our data as numbers.

Because we have to keep leading zeros we have to figure out a method we can do it.

Changing data type

First method

If you know exactly how many cells you want to paste you can change the cells data type beforehand.

First, select cells from A1 to A3 and then go to HOME >> Number.

From the drop-down menu select the data type. In our example, it will be Text.

Second method

If you already pasted the data, changing it text won’t help. In this case, you need to do it differently.

First, select the data and use the Ctrl + 1 keyboard shortcut.

It will open the Format Cells window.

Click the Custom category and in the Type field enter the desired amount of zeros.

In this example, we will enter five zeros because our largest number has 5 digits.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.