Add Leading Zeros in Google Sheets

This tutorial will teach us to add leading zeros (single and multiple) to a number. It means adding zeros before starting the number.

Add a Single Leading Zero

To add a single zero, you can use the CONCATENATE function to combine two values.

1. Enter = in a cell.
2. Type a zero (0).
3. Use an ampersand (&).
4. Refer to the cell with the number.
5. Hit enter to get the result.

This method only allows you to add a single zero. Even if you try to add more zeros directly into the function, it won’t show those zeros in the result.

=0&A1

Adding Multiple Zeros

If you want to add multiple zeros, you can use the TEXT function. In the below example, we have a number which has 8 digits. And now, you need to use TEXT so that if a number has 8 digits, the zeros should be 2, and if a number has 7 digits, the zeros should be 3.

1. Enter the TEXT function in a cell.
2. In the first argument, refer to the cell where you have the original number.
3. In the second argument, enter 10 zeros with double quotation marks “0000000000”.
4. Close the function and hit enter.

When you hit enter, it converts 12345678 into 0012345678 by adding two zeros.

And if the number has 7 digits, it will have three leading zeros.

=TEXT(A1,"0000000000")

Using Custom Format

This option is useful when converting the number by changing its format, not the actual value.

1. Select the cell or multiple cells.
2. Click on the more formats button.
   
3. Go to the ” Custom Number Format”.
   
4. In the Input Bar, enter “0000000000” and click “Apply”. Or you can also increase or decrease the digits in the format.
   

Once you apply the format, you will have the leading zeros according to the length of the number. If a number has 7 digits, you will have 3 zeros. The custom format method works just like the TEXT function we learned earlier.

⮞ Get the Sample Sheet