How to write leading zeros in Excel

In Excel, it is not possible to write zeros before a sequence of numbers. But in practice with some work, writing a zero at the beginning of the sequence is required. The following article will guide how to write leading zeros.

In Excel, it is not possible to write zeros before a sequence of numbers. But in practice with some work, writing a zero at the beginning of the sequence is required. The following article will guide how to write leading zeros.

Method 1: Enter apostrophes before the number sequence to enter.

Excel automatically removes the quotes, but there is a blue triangle warning above.

How to write leading zeros in Excel Picture 1How to write leading zeros in Excel Picture 1

Method 2: Choose a column format of type Text.

Step 1: Select the column to create numbers 0 -> Right-click -> Select Format Cells .

How to write leading zeros in Excel Picture 2How to write leading zeros in Excel Picture 2

Step 2: The Format Cells dialog box appears. At Category -> select Text -> OK .

How to write leading zeros in Excel Picture 3How to write leading zeros in Excel Picture 3

The result when you enter the leading zero does not disappear.

How to write leading zeros in Excel Picture 4How to write leading zeros in Excel Picture 4

Method 3: Use Custom.

For example: The rule has 10 digits if the value is only 5 digits then the first 5 digits enter is 0. If there are 9 digits, the first digit enters 0 -> Please follow this way:

Step 1: Select the column to write 0 -> Right-click -> Format Cells .

How to write leading zeros in Excel Picture 5How to write leading zeros in Excel Picture 5

Step 2: A dialog box appears, select Custom -> Item Type enter the number of digits 0 you want to display (assuming choose 12) -> OK .

How to write leading zeros in Excel Picture 6How to write leading zeros in Excel Picture 6

Result:

How to write leading zeros in Excel Picture 7How to write leading zeros in Excel Picture 7

If you enter 3 digits, Excel automatically inserts 12-3 = 9 leading zeros in front of it. Enter the number with 11 digits => with 1 leading zeros. If you enter 12 digits, no leading zeros are displayed.

Good luck!

4 ★ | 1 Vote