How to add leading zeros in excel

In excel, if you type a number as 01234, it will show 1234 immediately, this is default setting of excel. How to keep it? There are many ways to add leading zeros in excel. If you read this post, I think you will find the best way to do it.

I. Keep leading zeros in Excel as you type.

I usually type ( ’ ) before font of number that has 0 as leading. Example, ‘01234. I think, it is simple do it.
If you don’t want type “ ‘ “, you can set se cell format is text.
+ Select the cell where you want to input 0
+ Right click and select format cell. The format cell dialog will appear, select text option at number group and click OK.

After you type a zero before number, excel will show a small triangle in the top left corner of the cell. To remove that error, click the warning sign and click ignore error.

II. Custom number format.

+ Select the cell where you want to input 0
+ Right click and select format cell. Select custom option at number group and typing 0000 at type box and click OK. (For this example, I will create 4-digit number)

III. Using formula of excel.

1. Using CONCATENATE command.
Formula: CONCATENATE(text1, [text2], …)

For this example, formula is CONCATENATE(0,B3) -> Input 1 0 number at the front of B3

2. Using REPT and LEN command.
Formula: REPT(0, number of zeros-LEN(cell))&cell
For example, I will create I will create 7-digit number: =REPT(0,7-LEN(B3))&B3
LEN(B3): Count all characters in cell B3
REPT(0,7-LEN(B3)) adds the required number of zeros. To know how many zeros will be added, the number is 7 subtract count all characters in cell B3.

3. Use right command.
Formula: RIGHT(“00000” & cell, string_length)
In this formula: “00000”: the maximum number zeros you want to get.
Cell: The cell you want to add zeros.
String_length: how many characters the resulting string should contain.
Example: